((select avg(CLOSE) as price,

case when ID='apecoin' then 'ape' else ID end as token , date_trunc('day', RECORDED_HOUR) as date from

crosschain.core.fact_hourly_prices

where

ID in ('aave','apecoin','aave','frax','wei')

group by 2,3 )

union all

(select avg(PRICE) as price,

lower(SYMBOL)as token , date_trunc('day', RECORDED_HOUR) as date from

osmosis.core.ez_prices

group by 2,3 )

, t2 as

(select

case

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

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

when CURRENCY='polygon-uusdc' then 'usdc'

when CURRENCY='cmatic' then 'matic'

when CURRENCY='cusdc' then 'usdc'

when CURRENCY='avalanche-uusdc' then 'usdc'

when CURRENCY='usdt' then 'usdt'

when CURRENCY='aacre' then 'acre'

when CURRENCY='aevmos' then 'evmos'

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

when CURRENCY='afet' then 'fet'

when CURRENCY='stuatom' then 'atom'

when CURRENCY='ubcre' then 'cre'

when CURRENCY='echf' then 'chf'

when substr(CURRENCY,1,1)='u' and CURRENCY!='usdt' then substr(CURRENCY,2,len(CURRENCY))

when substr(CURRENCY,len(CURRENCY)-2,len(CURRENCY))='wei' and substr(CURRENCY,1,1)='w' then substr(CURRENCY,2,len(CURRENCY)-5)

when substr(CURRENCY,len(CURRENCY)-2,len(CURRENCY))='wei' and substr(CURRENCY,1,1)!='w' then substr(CURRENCY,1,len(CURRENCY)-4)