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

recorded_hour::date as day,

case id

when 'harmony' then 'ONE'

when 'stride-staked-atom' then 'ATOM'

when 'near' then 'NEAR'

else null end as token_symbol,