with price as

(select ID as tokens , avg(CLOSE) as price,date_trunc('day',RECORDED_HOUR) as date

from

crosschain.core.fact_hourly_prices

where

ID in ('frax','wei','wmatic','link','binancecoin','bitcoin','wrapped-avax','maker','rizon','polkadot')

and

group by 1,3)

union ALL

(select lower(SYMBOL) as tokens , avg(PRICE) as price, date_trunc('day',RECORDED_AT) as date from

osmosis.core.dim_prices

where

group by 1,3

)),

axl as

(select

case when

SUBSTR(CURRENCY, 0, 1) = 'u' then SUBSTR(CURRENCY, 2, len(CURRENCY)-1)

when CURRENCY='cusdc' then 'usdc'

when CURRENCY='wavax-wei' then 'wrapped-avax'

when CURRENCY='wftm-wei' then 'ftm'

when CURRENCY='wbtc-satoshi' then 'bitcoin'

when CURRENCY='wbnb-wei' then 'binancecoin'

when CURRENCY='dot-planck' then 'polkadot'

when CURRENCY='uni-wei' then 'uni'

when CURRENCY in('wmatic-wei','cmatic') then 'wmatic'