with token_price as (select date_trunc('day', hour) as dt, symbol, avg(price) as price_usd

from ethereum.core.fact_hourly_token_prices

where symbol in ('WETH', 'USDC', 'REVV', 'DAI')

group by 1, 2),

opensea_fees_collection as (select date_trunc('day', block_timestamp) as dt, tx_hash,

case when contract_address = '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' then 'WETH'

when contract_address = '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' then 'USDC'

when contract_address = '0x70c006878a5a50ed185ac4c87d837633923de296' then 'REVV'

when contract_address = '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' then 'DAI'

end as fee_token,

event_inputs:value/pow(10,18) as opensea_fees

from polygon.core.fact_event_logs

where origin_to_address = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d'

and event_name = 'Transfer'

and event_inputs:to in ('0x5b3256965e7c3cf26e11fcaf296dfc8807c01073', '0x8de9c5a032463c561423387a9648c5c7bcc5bc90'))

select count(distinct tx_hash) as num_txs,

sum(a.opensea_fees*b.price_usd) as opensea_fees_usd,

opensea_fees_usd/num_txs as avg_opensea_fees

from opensea_fees_collection a

join token_price b on

a.dt = b.dt and a.fee_token = b.symbol