-- Credit to hess with chains as (select date(block_timestamp) as date, 'Arbitrum' as chain, 'WETH' as symbol, tx_fee from arbitrum.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Optimism' as chain, 'WETH' as symbol, tx_fee from optimism.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Polygon' as chain, 'WMATIC' as symbol, tx_fee from polygon.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Avalanche' as chain, 'WAVAX' as symbol, tx_fee from avalanche.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'BSC' as chain, 'WBNB' as symbol, tx_fee from bsc.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Base' as chain, 'WETH' as symbol, tx_fee from base.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Ethereum' as chain, 'WETH' as symbol, tx_fee from ethereum.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Near' as chain, 'NEAR' as symbol, (TRANSACTION_FEE/pow(10,24)) as tx_fee from near.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' UNION select date(block_timestamp) as date, 'Solana' as chain, 'SOL' as symbol, (fee/pow(10,9)) as tx_fee from solana.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and succeeded = 'true' UNION select date(block_timestamp) as date, 'Apt' as chain, 'APT' as symbol, ((gas_used*gas_unit_price)/pow(10,8)) as tx_fee from aptos.core.fact_transactions where block_timestamp >= '2024-06-01' and block_timestamp < '2024-07-01' and success = 'true' ) , price as ( select date(hour) as date, symbol, avg(price) as avg_price from ethereum.price.ez_prices_hourly where symbol in ('WETH','WMATIC') group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from ethereum.price.ez_prices_hourly where symbol in ('WAVAX') group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from flow.price.ez_prices_hourly where symbol = 'FLOW' group by 1,2 UNION select date(hour) as date, 'NEAR' as symbol, avg(price) as avg_price from near.price.ez_prices_hourly where symbol = 'NEAR' group by 1,2 UNION select date(hour) as date, 'SOL' as symbol, avg(price) as avg_price from solana.price.ez_prices_hourly where token_address = 'So11111111111111111111111111111111111111112' and hour::date >= '2024-05-01' group by 1,2 UNION select date(HOUR) as date, 'Apt' as symbol, avg(price) as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' and HOUR::date >= '2024-06-01' and HOUR < '2024-07-01' group by 1,2) , final as (select chain, tx_fee*avg_price as fee_usd from chains a join price b on a.date = b.date and a.symbol = b.symbol ) select chain, case when chain = 'Near' then 'Near' else 'Other Chains' end as type, avg(fee_usd) as avg_fee from final group by 1 ' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Optimism' as chain, 'WETH' as symbol, tx_fee from optimism.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Polygon' as chain, 'WMATIC' as symbol, tx_fee from polygon.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Avalanche' as chain, 'WAVAX' as symbol, tx_fee from avalanche.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'BSC' as chain, 'WBNB' as symbol, tx_fee from bsc.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Base' as chain, 'WETH' as symbol, tx_fee from base.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Ethereum' as chain, 'WETH' as symbol, tx_fee from ethereum.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Near' as chain, 'NEAR' as symbol, (TRANSACTION_FEE/pow(10,24)) as tx_fee from near.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' UNION select date(block_timestamp) as date, 'Solana' as chain, 'SOL' as symbol, (fee/pow(10,9)) as tx_fee from solana.core.fact_transactions where block_timestamp::date >= '2024-06-01' and block_timestamp < '2024-07-01' and succeeded = 'true' UNION select date(block_timestamp) as date, 'Apt' as chain, 'APT' as symbol, ((gas_used*gas_unit_price)/pow(10,8)) as tx_fee from aptos.core.fact_transactions where block_timestamp >= '2024-06-01' and block_timestamp < '2024-07-01' and success = 'true' ) , price as ( select date(hour) as date, symbol, avg(price) as avg_price from ethereum.price.ez_prices_hourly where symbol in ('WETH','WMATIC') group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from ethereum.price.ez_prices_hourly where symbol in ('WAVAX') group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from flow.price.ez_prices_hourly where symbol = 'FLOW' group by 1,2 UNION select date(hour) as date, 'NEAR' as symbol, avg(price) as avg_price from near.price.ez_prices_hourly where symbol = 'NEAR' group by 1,2 UNION select date(hour) as date, 'SOL' as symbol, avg(price) as avg_price from solana.price.ez_prices_hourly where token_address = 'So11111111111111111111111111111111111111112' and hour::date >= '2024-05-01' group by 1,2 UNION select date(HOUR) as date, 'Apt' as symbol, avg(price) as avg_price from aptos.price.ez_prices_hourly where symbol = 'APT' and HOUR::date >= '2024-06-01' and HOUR < '2024-07-01' group by 1,2) , final as (select chain, tx_fee*avg_price as fee_usd from chains a join price b on a.date = b.date and a.symbol = b.symbol ) select chain, case when chain = 'Near' then 'Near' else 'Other Chains' end as type, avg(fee_usd) as avg_fee from final group by 1

Near Vs. Popular Chains (June)!