with priceTb as (

SELECT

date_trunc('day', hour) as p_date,

symbol,

avg(price) as price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol in ('USDT', 'USDC', 'MATIC', 'WETH', 'SNX', 'HOP', 'DAI', 'WBNB', 'WAVAX')

GROUP by 1,2

)

, hopTb as (

SELECT

date_trunc('day', block_timestamp) as date,

'Polygon' as chain,

count(DISTINCT tx_hash) as "Tx Count",

count(DISTINCT origin_from_address) as "User Count",

sum(TX_FEE * price) as "Fees Volume",

avg(TX_FEE * price) as "Avg Fees Volume",

sum(GAS_USED) as "GAS USED",

avg(GAS_USED) as "Avg GAS USED",

"Fees Volume" / "Tx Count" as "Avg Fee per Tx",

"Fees Volume" / "User Count" as "Avg Fee per User",

"GAS USED" / "Tx Count" as "Avg GAS USED per Tx",

"GAS USED" / "User Count" as "Avg GAS USED per User"

FROM polygon.core.fact_token_transfers t

left outer JOIN priceTb p on (t.block_timestamp::date = p.p_date)

JOIN polygon.core.fact_transactions b using(tx_hash, block_timestamp)

WHERE t.contract_address in (

lower('0xdAC17F958D2ee523a2206206994597C13D831ec7'),lower('0x3E4a3a4796d16c0Cd582C382691998f7c06420B6'), -- polygon, 'USDT'

lower('0x9F93ACA246F457916E49Ec923B8ed099e313f763'),lower('0x6c9a1ACF73bd85463A46B0AFc076FBdf602b690B'), -- polygon, 'USDT'

lower('0x712F0cf37Bdb8299D0666727F73a5cAbA7c1c24c'),lower('0x553bC791D746767166fA3888432038193cEED5E2'), -- polygon, 'MATIC

lower('0xb8901acB165ed027E32754E0FFe830802919727f'),lower('0xEcf268Be00308980B5b3fcd0975D47C4C8e1382a'), -- polygon, 'DAI',

lower('0x1fDeAF938267ca43388eD1FdB879eaF91e920c7A'),lower('0xb98454270065A31D71Bf635F6F7Ee6A518dFb849'), -- polygon, 'WETH'

lower('0xc5102fE9359FD9a28f877a67E36B0F050d81a3CC'),lower('0x58c61AeE5eD3D748a1467085ED2650B697A66234'), -- polygon, 'HOP',