date_trunc('day', hour) as block_day,

avg(PRICE) as avg_price

from ethereum.core.fact_hourly_token_prices

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

and block_day > current_date -240

group by block_day,SYMBOL),

B as (select date_trunc('day', block_timestamp) as block_day,

count(TX_HASH) as tx_count,

case

when CONTRACT_ADDRESS ='0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' then sum(RAW_AMOUNT/1e18)

when CONTRACT_ADDRESS ='0x70c006878a5a50ed185ac4c87d837633923de296' then sum(RAW_AMOUNT/1e18)

when CONTRACT_ADDRESS ='0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' then sum(RAW_AMOUNT/1e18)

when CONTRACT_ADDRESS ='0x2791bca1f2de4661ed88a30c99a7a9449aa84174' then sum(RAW_AMOUNT/1e6)

end as fee_amount,

CASE

when CONTRACT_ADDRESS ='0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' then 'WETH'

when CONTRACT_ADDRESS ='0x70c006878a5a50ed185ac4c87d837633923de296' then 'USDC'

when CONTRACT_ADDRESS ='0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' then 'DAI'

when CONTRACT_ADDRESS ='0x2791bca1f2de4661ed88a30c99a7a9449aa84174' then 'REVV'

end as asset_name

from polygon.core.fact_token_transfers

where to_address in ('0x8de9c5a032463c561423387a9648c5c7bcc5bc90' , '0x5b3256965e7c3cf26e11fcaf296dfc8807c01073')

and CONTRACT_ADDRESS in ('0x7ceb23fd6bc0add59e62ac25578270cff1b9f619','0x2791bca1f2de4661ed88a30c99a7a9449aa84174','0x8f3cf7ad23cd3cadbd9735aff958023239c6a063','0x70c006878a5a50ed185ac4c87d837633923de296')

and block_timestamp > current_date -240

group by block_day,asset_name,CONTRACT_ADDRESS),

C as (select sum(B.tx_count) as sum_tx,

sum(A.avg_price*B.fee_amount) as sum_fee

from A join B on A.SYMBOL=B.asset_name and A.block_day=B.block_day)

select sum_tx,sum_fee from C