select date_trunc('day', block_timestamp) as dt,

count(*) as tx_count,

count(case when status like '%FAIL%' then 1 else null end) as failed_tx_count,

count(case when status like '%SUCCESS%' then 1 else null end) as success_tx_count

from optimism.core.fact_transactions

where block_timestamp::date >= '2022-10-01' and block_timestamp::date <= '2022-11-08'

group by dt

order by dt asc

)

-- Considering 40 days

select sum(tx_count)/(40*1440) as tpm,

sum(tx_count-failed_tx_count)/(40*1440) as stpm,

sum(failed_tx_count)/(40*1440) as ftpm,

sum(failed_tx_count)/sum(tx_count)*100 as failed_rate,

sum(success_tx_count)/sum(tx_count)*100 as successful_rate

from all_txs