date_trunc('hour', hour) as hour,

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

and hour::date = current_date - 1

group by 1, 2

date_trunc('hour', recorded_hour) as hour,

case id

when 'harmony' then 'ONE'

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

when 'near' then 'NEAR'