-- FLOW with flow_total_avg_for_minute as ( select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from flow.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' ) , flow_success_per_min as (select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from flow.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' and tx_succeeded = True ) , base_flow as ( select * from ( select tx_per_min, 'Total' as type from (select * from flow_total_avg_for_minute) UNION select tx_per_min, 'Success' as type from (select * from flow_success_per_min) ) order by type ) , final_flow as ( select 'Flow' as chain, round(sum(case when type = 'Total' then tx_per_min end), 2) as tpm, round(sum(case when type = 'Success' then tx_per_min end), 2) as stpm, stpm/tpm*100 as percentage from base_flow ) -- ETH , eth_total_avg_for_minute as ( select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from ethereum.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' ) , eth_success_per_min as (select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from ethereum.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' and status = 'SUCCESS' ) , base_eth as ( select * from ( select tx_per_min, 'Total' as type from (select * from eth_total_avg_for_minute) UNION select tx_per_min, 'Success' as type from (select * from eth_success_per_min) ) order by type ) , final_eth as ( select 'Ethereum' as chain, round(sum(case when type = 'Total' then tx_per_min end), 2) as tpm, round(sum(case when type = 'Success' then tx_per_min end), 2) as stpm, stpm/tpm*100 as percentage from base_eth ) -- -- SOLANA -- , sol_total_avg_for_minute as ( -- select -- count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min -- from solana.core.fact_transactions -- where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' -- ) -- , sol_success_per_min as (select -- count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min -- from solana.core.fact_transactions -- where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' -- and succeeded = True -- ) -- , base_sol as ( -- select * from ( -- select tx_per_min, 'Total' as type from (select * from sol_total_avg_for_minute) -- UNION -- select tx_per_min, 'Success' as type from (select * from sol_success_per_min) -- ) order by type -- ) -- , final_sol as ( -- select 'Solana' as chain, -- sum(case when type = 'Total' then tx_per_min end) as tpm, -- sum(case when type = 'Success' then tx_per_min end) as stpm, -- stpm/tpm*100 as percentage -- from base_sol -- ) -- BASE , base_total_avg_for_minute as ( select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from base.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' ) , base_success_per_min as (select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from base.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' and status = 'SUCCESS' ) , base_base as ( select * from ( select tx_per_min, 'Total' as type from (select * from base_total_avg_for_minute) UNION select tx_per_min, 'Success' as type from (select * from base_success_per_min) ) order by type ) , final_base as ( select 'Base' as chain, round(sum(case when type = 'Total' then tx_per_min end), 2) as tpm, round(sum(case when type = 'Success' then tx_per_min end), 2) as stpm, stpm/tpm*100 as percentage from base_base ) , total_avg_for_minute as ( select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from optimism.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' ) , success_per_min as (select count(*)/count(DISTINCT block_timestamp::date)/1440 as tx_per_min from optimism.core.fact_transactions where block_timestamp::date < CURRENT_DATE and block_timestamp::date >= '2023-08-01' and status = 'SUCCESS' ) , base as ( select * from ( select tx_per_min, 'Total' as type from (select * from total_avg_for_minute) UNION select tx_per_min, 'Success' as type from (select * from success_per_min) ) order by type ) , final_opt as ( select 'Optimism' as chain, round(sum(case when type = 'Total' then tx_per_min end), 2) as tpm, round(sum(case when type = 'Success' then tx_per_min end), 2) as stpm, stpm/tpm*100 as percentage from base ) , final as ( select * from ( select * from final_flow UNION select * from final_eth UNION -- select * from final_sol -- UNION select * from final_opt UNION select * from final_base ) order by chain ) select * from final