date_trunc('day', block_timestamp) as date,

count(*) / 1440 as avg_tpm,

count(*) as no_of_txs,

count(case when status = 'SUCCESS' then 1 end) as no_of_successful_txs,

count(case when status = 'SUCCESS' then 1 end) / count(*) * 100 as successful_txs_pct,

count(case when status <> 'SUCCESS' then 1 end) as no_of_failed_txs,

count(case when status <> 'SUCCESS' then 1 end) / count(*) * 100 as failed_txs_pct

from arbitrum.core.fact_transactions

where block_timestamp >= CURRENT_DATE - interval '30 days'

and block_timestamp < CURRENT_DATE

group by 1

order by 1 desc