SELECT

date_trunc('day', block_timestamp) as day,

count(DISTINCT tx_hash) as "Total Transactions"

FROM berachain.testnet.fact_transactions

GROUP BY day

),

daily_succeeded_tx AS

SELECT

date_trunc('day', block_timestamp) as day,

count(DISTINCT tx_hash) as "Total Succeeded Transactions"

FROM berachain.testnet.fact_transactions

WHERE TX_SUCCEEDED = 'TRUE'

GROUP BY day

SELECT

a.day,

b."Total Succeeded Transactions" / a."Total Transactions" * 100 as "tx Success Rate %"

FROM daily_tx a

JOIN daily_succeeded_tx b

ON a.day = b.day

ORDER BY 1