select hour::date as day,

avg (price) as OPUSDPRice

from optimism.core.fact_hourly_token_prices

where symbol = 'OP'

group by 1),

ethpricet as (

select hour::date as day,

avg (price) as ETHUSDPrice

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1),

opethpricet as (

select t1.day,

OPUSDPrice/ETHUSDPrice as OPETHPrice

from oppricet t1 join ethpricet t2 on t1.day = t2.day

order by 1),

maintable as (

select tx_hash,

buyer_address,

seller_address,

price_usd,

case when currency_symbol = 'ETH' then price when currency_symbol = 'OP' then price*OPETHPrice end as ETH_Price

from optimism.core.ez_nft_sales t1 join opethpricet t2 on t1.block_timestamp::date = t2.DAY

where price_usd > 0),

userstable as (

select seller_address,

sum (price_usd) as Total_USD_Volume,

sum (eth_price) as Total_ETH_Volume

from maintable

group by 1)