-- forked from Masi / Average Fee @ https://flipsidecrypto.xyz/Masi/q/wBtwOp-kR1NO/average-fee -- 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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-01' and status = 'SUCCESS' UNION select date(block_timestamp) as date, 'Sei' as chain, 'SEI' as symbol, split(fee,'usei')[0]/pow(10,6) as tx_fee from sei.core.fact_transactions where block_timestamp::date >= '2024-09-01' and block_timestamp < '2024-10-01' and TX_SUCCEEDED = 'TRUE' and fee != '0usei' 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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-09-01' and block_timestamp < '2024-10-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-04-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-09-01' and HOUR < '2024-10-01' group by 1,2 UNION select date(RECORDED_HOUR) as date, 'SEI' as symbol, avg(price) as avg_price from osmosis.price.ez_prices where symbol = 'SEI' group by 1,2) , final as (select a.date, chain, tx_fee*avg_price as fee_usd from chains a join price b on a.date = b.date and a.symbol = b.symbol ), final_two as ( select date, chain, sum(fee_usd) as fee_usd_ from final group by all ) select chain, case when chain = 'Near' then 'Near' else 'Other Chains' end as type, avg(fee_usd_) as avg_fee from final_two group by 1 order by 3 asc

Query	Creator	Created
marked-ivory aptos	sam	Oct 14, 2024
2024-08-26 06:59 PM copy compound aptos ethereum evm defi	sam	Aug 29, 2024
Grail Troubleshoot copy copy copy aptos bridge	sam	May 10, 2024
2023-12-19 10:29 AM aptos	sam	Dec 19, 2023
2024-05-11 02:02 AM aptos blast l2 evm	sam	May 11, 2024
Average Fee copy flow aptos near bsc prices	sam	Oct 29, 2024
2023-12-07 03:35 AM aptos	sam	Dec 7, 2023
2024-05-10 10:28 PM aptos bridge	sam	May 10, 2024

sam