avg (price) as eth_price_

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1),

matic_price as (select hour::date as day,

avg (price) as matic_price_

from ethereum.core.fact_hourly_token_prices

where symbol = 'MATIC'

group by 1)

select 'Optimism' as Net,

sum (tx_fee * eth_price_) as Total_Fee,

avg (tx_fee * eth_price_) as Average_Fee

from optimism.core.fact_transactions a join eth_price b on a.block_timestamp::date = b.day

where block_timestamp >= '2022-06-01'

group by 1

union all

select 'Arbitrum' as Net,

sum (tx_fee * eth_price_) as Total_Fee,

avg (tx_fee * eth_price_) as Average_Fee

from arbitrum.core.fact_transactions a join eth_price b on a.block_timestamp::date = b.day

where block_timestamp >= '2022-06-01'

group by 1

union all

select 'Polygon' as Net,

sum (tx_fee * matic_price_) as Total_Fee,

avg (tx_fee * matic_price_) as Average_Fee

from polygon.core.fact_transactions a join matic_price b on a.block_timestamp::date = b.day

where block_timestamp >= '2022-06-01'

group by 1