with tokenprice as (

SELECT

date_trunc('day', HOUR) as datee,

avg(PRICE) as price,

from crosschain.price.ez_hourly_token_prices

where hour > current_date - 90

GROUP BY datee, SYMBOL

),

main as(

SELECT

block_timestamp,

DIRECTION,

BRIDGE,

case

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceFTM') then 'FTM'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceAVAX') then 'AVAX'

when TOKEN_CONTRACT in('A.231cc0dbbcffc4b7.ceUSDT','A.cfdd90d4a00f7b5b.TeleportedTetherToken','A.9c6f94adf47904b5.UsdcUsdtSwapPair') then 'USDT'

when TOKEN_CONTRACT in( 'A.b19436aae4d94622.FiatToken','A.0f9df91c9121c460.BloctoToken') then 'USD'

when TOKEN_CONTRACT in ('A.d01e482eb680ec9f.REVV') then 'REVV'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceBUSD') then 'BUSD'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceDAI') then 'DAI'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceWETH') then 'ETH'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceBNB') then 'BNB'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.ceMATIC') then 'MATIC'

when TOKEN_CONTRACT in ('A.142fa6570b62fd97.StarlyToken') then 'STARLY'

when TOKEN_CONTRACT in ('A.1654653399040a61.FlowToken') then 'FLOW'

when TOKEN_CONTRACT in ('A.231cc0dbbcffc4b7.RLY') then 'RLY'

end AS Token,

TX_ID,

FLOW_WALLET_ADDRESS,

BLOCKCHAIN,

from flow.defi.ez_bridge_transactions