CASE

when TOKEN_CONTRACT ilike '%BloctoToken' then 'BLT'

when TOKEN_CONTRACT ilike '%RLY' then 'RLY'

when TOKEN_CONTRACT ilike '%REVV' then 'REVV'

when TOKEN_CONTRACT ilike '%StarlyToken' then 'STARLY'

when TOKEN_CONTRACT ilike '%lowToken' then 'FLOW'

when TOKEN_CONTRACT ilike '%ceAVAX' then 'AVAX'

when TOKEN_CONTRACT ilike '%ceBNB' then 'BNB'

when TOKEN_CONTRACT ilike '%ceBNB' then 'BNB'

when TOKEN_CONTRACT ilike '%ceBUSD' then 'BUSD'

when TOKEN_CONTRACT ilike '%ceUSDT' or TOKEN_CONTRACT ilike '%TeleportedTetherToken' then 'USDT'

when TOKEN_CONTRACT ilike '%ceWBTC' then 'WBTC'

when TOKEN_CONTRACT ilike '%ceFTM' then 'FRM'

when TOKEN_CONTRACT ilike '%ceWETH' then 'WETH'

when TOKEN_CONTRACT ilike '%ceMATIC' then 'MATIC'

else SUBSTR(TOKEN_CONTRACT, 20)

end as TOKEN,

sum(AMOUNT) AS amount

from flow.core.fact_bridge_transactions

group by 1),

prices AS (

SELECT DATE_TRUNC(DAY,TIMESTAMP) AS DAY,

SYMBOL AS TOKEN,

AVG(PRICE_USD) AS PRICE

FROM flow.core.fact_prices

GROUP BY 1,2

UNION ALL

SELECT DATE_TRUNC(DAY,HOUR) AS day,

SYMBOL AS TOKEN,

AVG(PRICE) AS PRICE

FROM ethereum.core.fact_hourly_token_prices where SYMBOL not in ('RLY','BLT','REVV','STARLY','FLOW')

GROUP BY 1,2

select