select hour::date as day,

avg (price) as ETHPrice

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1)

select 'Optimism' as blockchain,

date_trunc(day,block_timestamp) as date,

count (distinct tx_hash) as TX_Count,

count (distinct from_address) as Users_Count,

sum (tx_fee) as Total_ETH_Fee,

avg (tx_fee) as Average_ETH_Fee,

max (tx_fee) as Maximum_ETH_Fee,

median (tx_fee) as Median_ETH_fee,

sum (tx_fee*ethprice) as Total_USD_Fee,

avg (tx_fee*ethprice) as Average_USD_Fee,

max (tx_fee*ethprice) as Maximum_USD_Fee,

median (tx_fee*ethprice) as Median_USD_Fee,

sum (tx_count) over (order by date) as Cumulative_TX_Count,

sum (total_eth_fee) over (order by date) as Cumulative_ETH_fee,

sum (total_usd_fee) over (order by date) as Cumulative_USD_Fee

from optimism.core.fact_transactions t1 join ethpricet t2 on t1.block_timestamp::date = t2.day

where status = 'SUCCESS'

group by 1,2

union all

select 'Ethereum' as blockchain,

date_trunc(day,block_timestamp) as date,

count (distinct tx_hash) as TX_Count,

count (distinct from_address) as Users_Count,

sum (tx_fee) as Total_ETH_Fee,

avg (tx_fee) as Average_ETH_Fee,

max (tx_fee) as Maximum_ETH_Fee,

median (tx_fee) as Median_ETH_fee,