SELECT hour, price

FROM optimism.core.fact_hourly_token_prices

WHERE symbol = 'ETH'

), eth_fees as (

SELECT a.block_timestamp::date as date,

sum(b.tx_fee) as total_eth_fee,

avg(b.tx_fee) as avg_fee,

avg(b.tx_fee * price) as avg_fee_usd,

sum(b.tx_fee * price) as total_fee_usd

FROM ethereum.sushi.ez_swaps as a JOIN

ethereum.core.fact_transactions as b ON a.tx_hash = b.tx_hash JOIN

eth_prices as c ON date_trunc(hour, a.block_timestamp) = c.hour

WHERE a.block_timestamp > '2022-06-01'

GROUP BY 1

), op_fees as (

SELECT a.block_timestamp::date as date,

sum(b.tx_fee) as total_eth_fee,

avg(b.tx_fee) as avg_fee,

avg(b.tx_fee * price) as avg_fee_usd,

sum(b.tx_fee * price) as total_fee_usd

FROM optimism.sushi.ez_swaps as a JOIN

optimism.core.fact_transactions as b ON a.tx_hash = b.tx_hash JOIN

eth_prices as c ON date_trunc(hour, a.block_timestamp) = c.hour

WHERE a.block_timestamp > '2022-06-01'

GROUP BY 1

SELECT

a.date,

a.total_eth_fee as "Total Fees (Ethereum Network)",

b.total_eth_fee as "Total Fees (Optimism Network)",

a.avg_fee as "Average Fee on Ethereum Network",

b.avg_fee as "Average Fee on Optimism Network",

a.total_fee_usd as "Ethereum Fees USD",

b.total_fee_usd as "Optimism Fees USD",

a.avg_fee_usd as "Avg Fee/Tx Ethereum",