--credit: mhm with algorand as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(fee) as hourly_fee, count(distinct block_id) as hourly_block_count, count(distinct tx_sender) as hourly_tx_sender_count, count(distinct tx_group_id) as hourly_tx_count from algorand.core.fact_transaction where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select block_hour as hour, _algo_price from algorand.core.ez_price_pool_balances where block_hour :: date >= current_date - 30 and block_hour :: date <> CURRENT_DATE order by 1 ) select tab1.hour as hour, hourly_fee * _algo_price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * _algo_price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * _algo_price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * _algo_price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Algorand' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> ARBITRUM <-- ------------------------------ arbitrum as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(tx_fee) as hourly_fee, count(distinct block_number) as hourly_block_count, count(distinct tx_hash) as hourly_tx_count, count(distinct from_address) as hourly_tx_sender_count from arbitrum.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select hour, price from ethereum.core.fact_hourly_token_prices where hour :: date >= current_date - 30 and hour :: date <> CURRENT_DATE and symbol = 'WETH' order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Arbitrum' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> AVALANCHE <-- ------------------------------ avalanche as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(tx_fee) as hourly_fee, count(distinct block_number) as hourly_block_count, count(distinct from_address) as hourly_tx_sender_count, count(distinct tx_hash) as hourly_tx_count from avalanche.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select date_trunc('hour', block_timestamp) as hour, sum(amount_in)/ sum(amount_out) as price from avalanche.sushi.ez_swaps where ( symbol_in = 'USDC' or symbol_in = 'USDT' OR symbol_in = 'MIM' OR symbol_in = 'USDC.e' OR symbol_in = 'USDT.e' ) and symbol_out = 'WAVAX' and block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Avalanche' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> BSC <-- ------------------------------ bsc as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(tx_fee) as hourly_fee, count(distinct block_number) as hourly_block_count, count(distinct from_address) as hourly_tx_sender_count, count(distinct tx_hash) as hourly_tx_count from bsc.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select date_trunc('hour', block_timestamp) as hour, sum(amount_in)/ sum(amount_out) as price from bsc.sushi.ez_swaps where ( symbol_in = 'USDC' or symbol_in = 'USDT' OR symbol_in = 'MIM' OR symbol_in = 'BUSD' OR symbol_in = 'DAI' ) and symbol_out = 'WBNB' and block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'BSC' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> ETHEREUM <-- ------------------------------ ethereum as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(tx_fee) as hourly_fee, count(distinct block_number) as hourly_block_count, count(distinct from_address) as hourly_tx_sender_count, count(distinct tx_hash) as hourly_tx_count from ethereum.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select hour, price from ethereum.core.fact_hourly_token_prices where hour :: date >= current_date - 30 and hour :: date <> CURRENT_DATE and symbol = 'WETH' order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Ethereum' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> OPTIMISM <-- ------------------------------ optimism as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(tx_fee) as hourly_fee, count(distinct block_number) as hourly_block_count, count(distinct from_address) as hourly_tx_sender_count, count(distinct tx_hash) as hourly_tx_count from optimism.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select hour, price from ethereum.core.fact_hourly_token_prices where hour :: date >= current_date - 30 and hour :: date <> CURRENT_DATE and symbol = 'WETH' order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Optimism' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> FLOW <-- ------------------------------ flow as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(gas_limit)/ pow(10, 8) as hourly_fee, count(distinct BLOCK_HEIGHT) as hourly_block_count, count(distinct proposer) as hourly_tx_sender_count, count(distinct tx_id) as hourly_tx_count from flow.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select date_trunc('hour', timestamp) as hour, avg(price_usd) as price from flow.core.fact_prices where timestamp :: date >= current_date - 30 and timestamp :: date <> current_date and symbol = 'FLOW' group by 1 order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Flow' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------------------------------------------------------------------------------------------------------------------- -- NEAR -- ------------------------------------------------------------------------------------------------------------------------------------------- near as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(transaction_fee)/ pow(10, 24) as hourly_fee, count(distinct BLOCK_ID) as hourly_block_count, count(distinct TX_SIGNER) as hourly_tx_sender_count, count(distinct tx_hash) as hourly_tx_count from near.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select date_trunc('hour', timestamp) as hour, avg(price_usd) as price from near.core.fact_prices where timestamp :: date >= current_date - 30 and timestamp :: date <> current_date and symbol = 'wNEAR' group by 1 order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'NEAR' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> OSMOSIS <-- ------------------------------ osmosis as ( select date_trunc('hour', block_timestamp) as hour, SUM( price * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) as hourly_fee_usd, SUM( 1 * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) as hourly_fee, count(distinct BLOCK_ID) as hourly_block_count, ( SUM( price * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) )/ count(distinct BLOCK_ID) as hourly_fee_per_block_usd, ( SUM( 1 * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) )/( count(distinct BLOCK_ID) ) as hourly_fee_per_block, count(distinct tx_from) as hourly_tx_sender_count, ( SUM( price * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) )/( count(distinct tx_from) ) as hourly_fee_per_user_usd, ( SUM( 1 * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) )/( count(distinct tx_from) ) as hourly_fee_per_user, count(distinct tx_id) as hourly_tx_count, ( SUM( price * cast( replace(fee, 'uosmo', '') as int )/ pow(10, 6) ) )/( count(distinct tx_id) ) as hourly_fee_per_tx_usd, 'Osmosis' as blockchain from osmosis.core.fact_transactions left outer join ( select date_trunc('hour', recorded_hour) as hour, avg(close) as price from crosschain.core.fact_hourly_prices where id like 'osmosis' group by 1 ) on date_trunc('hour', block_timestamp)= hour where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date and fee like '%uosmo%' group by 1 order by 1 ), ------------------------------ --> POLYGON <-- ------------------------------ polygon as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(tx_fee) as hourly_fee, count(distinct block_number) as hourly_block_count, count(distinct from_address) as hourly_tx_sender_count, count(distinct tx_hash) as hourly_tx_count from polygon.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select hour, price from ethereum.core.fact_hourly_token_prices where hour :: date >= current_date - 30 and hour :: date <> CURRENT_DATE and symbol = 'MATIC' order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Polygon' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), ------------------------------ --> SOLANA <-- ------------------------------ solana as ( with tab1 as ( select date_trunc('hour', block_timestamp) as hour, sum(fee)/ pow(10, 9) as hourly_fee, count(distinct block_id) as hourly_block_count, count(distinct signers[0]) as hourly_tx_sender_count, count(distinct tx_id) as hourly_tx_count from solana.core.fact_transactions where block_timestamp :: date >= current_date - 30 and block_timestamp :: date <> current_date group by 1 order by 1 ), tab2 as ( select recorded_hour as hour, close as price from solana.core.fact_token_prices_hourly where symbol = 'SOL' and recorded_hour :: date >= current_date - 30 and recorded_hour :: date <> current_date and provider = 'coinmarketcap' order by 1 ) select tab1.hour as hour, hourly_fee * price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count, (hourly_fee * price)/ hourly_block_count as hourly_fee_per_block_usd, hourly_fee / hourly_block_count as hourly_fee_per_block, hourly_tx_sender_count, (hourly_fee * price)/ hourly_tx_sender_count as hourly_fee_per_user_usd, hourly_fee / hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count, (hourly_fee * price)/ hourly_tx_count as hourly_fee_per_tx_usd, 'Solana' as blockchain from tab1 left join tab2 on tab1.hour = tab2.HOUR order by 1 ), all_hourly_data as ( select * from algorand union all select * from arbitrum union all select * from bsc union all select * from ethereum union all select * from optimism union all select * from flow union all select * from near union all select * from osmosis union all select * from polygon union all select * from solana ) select date_trunc('day', hour) as date, blockchain, sum(HOURLY_FEE_USD) as "Daily Amount of Fee [USD]", sum(HOURLY_BLOCK_COUNT) as "Daily Number of Block", "Daily Amount of Fee [USD]"/"Daily Number of Block" as "Daily Fee Per Block [USD]", sum(HOURLY_TX_SENDER_COUNT) as "Daily Number of Senders Transactions", "Daily Amount of Fee [USD]"/"Daily Number of Senders Transactions" as "Daily Fee Per Users [USD]", sum(HOURLY_TX_COUNT) as "Daily Number of Transactions", "Daily Amount of Fee [USD]"/"Daily Number of Transactions" as "Daily Fee Per of Transaction [USD]", sum("Daily Amount of Fee [USD]") over (partition by blockchain order by date) as "Cumulative Amount of Fee [USD]", sum("Daily Number of Block") over (partition by blockchain order by date) as "Cumulative Number of Block", sum("Daily Number of Senders Transactions") over (partition by blockchain order by date) as "Cumulative Number of Senders Transactions", sum("Daily Number of Transactions") over (partition by blockchain order by date) as "Cumulative Number of Transactions", sum("Daily Amount of Fee [USD]") over (order by date) as "Total Amount of Fee [USD]", sum("Daily Number of Block") over (order by date) as "Total Number of Block", sum("Daily Number of Senders Transactions") over (order by date) as "Total Number of Senders", sum("Daily Number of Transactions") over (order by date) as "Total Number of Transactions" from all_hourly_data group by 1,2 order by date desc

jimmysand