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

)

select dt,

tx_count/1440 as tpm,

(tx_count-failed_tx_count)/1440 as stpm,

(failed_tx_count)/1440 as ftpm,

failed_tx_count/tx_count*100 as failed_rate,

success_tx_count/tx_count*100 as successful_rate

from all_txs