select hour::date as day,

avg (price) as ETHPrice

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1),

maintable as (

select 'Optimism' as blockchain,

from_address,

count (distinct tx_hash) as TX_Count,

sum (tx_fee) as Total_ETH_Fee,

sum (tx_fee*ethprice) as Total_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,

from_address,

count (distinct tx_hash) as TX_Count,

sum (tx_fee) as Total_ETH_Fee,

sum (tx_fee*ethprice) as Total_USD_Fee

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

where status = 'SUCCESS'

group by 1,2)

select from_address,

tx_count,

total_eth_fee,

from maintable

where blockchain = 'Ethereum'

order by 2 DESC

limit 10