select

hour,

case token_address

when '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then 'A.231cc0dbbcffc4b7.ceWETH' --wrapped eth

when '0x4e15361fd6b4bb609fa63c81a2be19d873717870' then 'A.231cc0dbbcffc4b7.ceFTM' --ftm

when '0xdac17f958d2ee523a2206206994597c13d831ec7' then 'A.231cc0dbbcffc4b7.ceUSDT' -- usdt

when '0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0' then 'A.231cc0dbbcffc4b7.ceMATIC' --matic

when '0x85f138bfee4ef8e540890cfb48f620571d67eda3' then 'A.231cc0dbbcffc4b7.ceAVAX' --wrapped avax

when '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' then 'A.231cc0dbbcffc4b7.ceWBTC' --wrapped btc

when '0x4fabb145d64652a948d72533023f6e7a623c7c53' then 'A.231cc0dbbcffc4b7.ceBUSD' --wrapped busd

when '0x418d75f65a02b3d53b2418fb8e1fe493759c7605' then 'A.231cc0dbbcffc4b7.ceBNB' -- wrapped bnb

when '0x17a79792fe6fe5c95dfe95fe3fcee3caf4fe4cb7' then 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' --AMM usdt to avoid using the same address for two contracts, im not sure what could happen

end as token_contract,

price

from ethereum.core.fact_hourly_token_prices

where token_address in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x4e15361fd6b4bb609fa63c81a2be19d873717870', '0xdac17f958d2ee523a2206206994597c13d831ec7',

'0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0','0x85f138bfee4ef8e540890cfb48f620571d67eda3',

'0x2260fac5e5542a773aa44fbcfedf7c193bc2c599', '0x4fabb145d64652a948d72533023f6e7a623c7c53', '0x418d75f65a02b3d53b2418fb8e1fe493759c7605', '0x17a79792fe6fe5c95dfe95fe3fcee3caf4fe4cb7')

group by 1, 2, 3),

flow as (select

date_trunc('hour',timestamp) as hour,

case token

when 'Flow' then 'A.1654653399040a61.FlowToken'

when 'Blocto Token' then 'A.0f9df91c9121c460.BloctoToken'

when 'Starly' then 'A.142fa6570b62fd97.StarlyToken'

when 'Sportium' then 'A.475755d2c9dccc3a.TeleportedSportiumToken'

when 'Rally' then 'A.231cc0dbbcffc4b7.RLY'

when 'REVV' then 'A.d01e482eb680ec9f.REVV'

end as Token_contract,

avg(price_usd) as price

from flow.core.fact_prices

group by 1, 2)

select