hour::date as day,

case token_address

when '0xbc396689893d065f41bc2c6ecbee5e0085233447' then 'PERP'

when '0x2f6081e3552b1c86ce4479b80062a1dda8ef23e3' then 'USD'

when '0x7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9' then 'AAVE'

when '0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' then 'MATIC'

when '0xb8c77482e45f1f44de1745f52c74426c631bdd52' then 'BNB'

when '0x514910771af9ca656af840dff83e8264ecf986ca' then 'LINK'

when '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' then 'WBTC'

when '0xd533a949740bb3306d119cc777fa900ba034cd52' then 'CRV'

when '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then 'WETH'

when '0x4e15361fd6b4bb609fa63c81a2be19d873717870' then 'FTM'

when '0x3845badade8e6dff049820680d1f14bd3903a5d0' then 'SAND'

when '0xf9e293d5d793ddc1ae4f778761e0b3e4aa7cf2dd' then 'Dodge'

when '0xbd31EA8212119f94A611FA969881CBa3EA06Fa3d' then 'LUNA'

when '0x4d224452801aced8b2f0aebe155379bb5d594381' then 'APE'

when '0xD31a59c85aE9D8edEFeC411D448f90841571b89c' then 'SOL'

when '0x85f138bfee4ef8e540890cfb48f620571d67eda3' then 'AVAX'

when '0x5c147e74d63b1d31aa3fd78eb229b65161983b2b' then 'FLOW'

else null end as token_symbol,

avg (price) as price_usd

from ethereum.core.fact_hourly_token_prices

where token_symbol is not null

group by 1, 2

),

t2 as (

block_timestamp,

tx_hash,

origin_from_address,

case contract_address

when '0x9482aafdced6b899626f465e1fa0cf1b1418d797' then 'PERP'

when '0xc84da6c8ec7a57cd10b939e79eaf9d2d17834e04' then 'USD'