WITH op_ as (SELECT 'optimism' as chain, date_trunc('day', block_timestamp) as dt, count(*) as tx_count, count(case when status like '%FAIL%' then 1 else null end) as fail_tx_count FROM optimism.core.fact_transactions WHERE block_timestamp::date >= '2022-07-01' AND block_timestamp::date <= '2022-07-31' GROUP BY dt ) , n_ as ( SELECT 'near' as chain , date_trunc('day', block_timestamp) as dt , count(*) as tx_count , count(CASE WHEN tx_receipt[0]:"outcome":"status":"Failure" is not null THEN 1 END) as fail_tx_count FROM flipside_prod_db.mdao_near.transactions WHERE block_timestamp::date >= '2022-07-01' AND block_timestamp::date <= '2022-07-31' GROUP BY dt ) , e_ as ( SELECT 'ethereum' as chain , date_trunc('day', block_timestamp) as dt , count(*) as tx_count , count(CASE WHEN status <> 'SUCCESS' THEN 1 END) as fail_tx_count FROM ethereum.core.fact_transactions WHERE block_timestamp::date >= '2022-07-01' AND block_timestamp::date <= '2022-07-31' GROUP BY dt ) , p_ as ( SELECT 'polygon' as chain , date_trunc('day', block_timestamp) as dt , count(*) as tx_count , count(CASE WHEN success <> 'TRUE' THEN 1 END) as fail_tx_count FROM flipside_prod_db.polygon.transactions WHERE block_timestamp::date >= '2022-07-01' AND block_timestamp::date <= '2022-07-31' GROUP BY dt ) , f_ as ( SELECT 'flow' as chain , date_trunc('day', block_timestamp) as dt , count(*) as tx_count , count(CASE WHEN tx_succeeded <> 'TRUE' THEN 1 END) as fail_tx_count FROM flow.core.fact_transactions WHERE block_timestamp::date >= '2022-07-01' AND block_timestamp::date <= '2022-07-31' GROUP BY dt ) , s_ as ( SELECT 'solana' as chain , date_trunc('day', block_timestamp) as dt , count(*) as tx_count , count(CASE WHEN succeeded <> 'TRUE' THEN 1 END) as fail_tx_count FROM solana.core.fact_transactions WHERE block_timestamp::date >= '2022-07-01' AND block_timestamp::date <= '2022-07-31' GROUP BY dt ) , combined_ as ( SELECT * FROM op_ UNION SELECT * FROM n_ UNION SELECT * FROM e_ UNION SELECT * FROM f_ UNION SELECT * FROM p_ UNION SELECT * FROM s_ ) , first_week_stpm_ as ( SELECT chain , '1st week' as duration , sum(tx_count-fail_tx_count)/(8*1440) as stpm FROM combined_ WHERE dt >= '2022-07-01' AND dt <= '2022-07-08' GROUP BY chain ) , second_week_stpm_ as ( SELECT chain , '2nd week' as duration , sum(tx_count-fail_tx_count)/(8*1440) as stpm FROM combined_ WHERE dt >= '2022-07-09' AND dt <= '2022-07-16' GROUP BY chain ) , third_week_stpm_ as ( SELECT chain , '3rd week' as duration , sum(tx_count-fail_tx_count)/(8*1440) as stpm FROM combined_ WHERE dt >= '2022-07-17' AND dt <= '2022-07-24' GROUP BY chain ) , fourth_week_stpm_ as ( SELECT chain , '4th week' as duration , sum(tx_count-fail_tx_count)/(8*1440) as stpm FROM combined_ WHERE dt >= '2022-07-25' AND dt <= '2022-07-31' GROUP BY chain ) SELECT * FROM first_week_stpm_ UNION SELECT * FROM second_week_stpm_ UNION SELECT * FROM third_week_stpm_ UNION SELECT * FROM fourth_week_stpm_ ORDER BY stpm desc

Optimistic Bears(Redux)