(select

case

when id='moonbeam' then 'wglmr-wei'

when id='wmatic' then 'wmatic-wei'

when id='avalanche-2' then 'wavax-wei'

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

when id='fantom' then 'wftm-wei'

when id='oec-binance-coin' then 'wbnb-wei'

when id='ethereum' then 'weth-wei'

end as token , avg(CLOSE) as price ,

date_trunc('day',RECORDED_HOUR ) as date

from

crosschain.core.fact_hourly_prices

where

ID in ('moonbeam','wmatic','avalanche-2','bitcoin','fantom','oec-binance-coin','ethereum')

group by 1,3

)

select

sum ((AMOUNT/pow(10,DECIMAL))*price ) as volume ,

date_trunc(day,BLOCK_TIMESTAMP) as date, CURRENCY

from axelar.core.fact_transfers a inner join price b on a.CURRENCY=b.token

where

((AMOUNT/pow(10,DECIMAL))*price)<2000000 AND substr(CURRENCY,1,1)='w' and TRANSFER_TYPE='IBC_TRANSFER_OUT'

group by 2,3