from optimism.core.fact_transactions where BLOCK_TIMESTAMP::date >= '2022-07-01' and BLOCK_TIMESTAMP::date <= '2022-07-31' and STATUS = 'SUCCESS' group by 1),

failed as ( select date(BLOCK_TIMESTAMP) as p_date , count(DISTINCT(tx_hash)) as failed from optimism.core.fact_transactions where BLOCK_TIMESTAMP::Date >= '2022-07-01' and BLOCK_TIMESTAMP::date < '2022-07-31' and STATUS = 'FAIL' group by 1),

total_tx as ( select date(BLOCK_TIMESTAMP) as d_date, from_address , count(DISTINCT(tx_hash)) as total_tx from optimism.core.fact_transactions where BLOCK_TIMESTAMP::Date >= '2022-07-01' and BLOCK_TIMESTAMP::date < '2022-07-31'

group by 1,2),

average as ( select d_date, avg(total_tx) as average_tx , sum(total_tx) as total , total/86400 as TPS, total/1440 as TPM from total_tx where d_date >= '2022-07-01' and d_date < '2022-07-31'

group by 1)

select date, success , failed , total , average_tx, tps,tpm, sum(failed) over (order by date asc) as cum_failed, sum(success) over (order by date asc) as cum_success from success a left outer join failed b on a.date = b.p_date

left outer join average c on a.date = c.d_date

order by 1