select date(timestamp) as date, symbol as token, round(avg(price_usd),2) as price from flow.core.fact_prices group by date, symbol

union

select date(hour) as date, symbol as token, round(avg(price),2) as price from ethereum.core.fact_hourly_token_prices group by date, symbol

),

outflows as (

select date(block_timestamp) as day,

case when token_contract = 'A.231cc0dbbcffc4b7.ceBNB' then 'BNB'

when token_contract = 'A.231cc0dbbcffc4b7.ceWBTC' then 'WBTC'

when token_contract = 'A.231cc0dbbcffc4b7.ceUSDT' then 'USDT'

when token_contract = 'A.231cc0dbbcffc4b7.ceWETH' then 'WETH'

when token_contract = 'A.231cc0dbbcffc4b7.ceFTM' then 'FTM'

when token_contract = 'A.231cc0dbbcffc4b7.ceAVAX' then 'AVAX'

when token_contract = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then 'USDT'

when token_contract = 'A.1654653399040a61.FlowToken' then 'FLOW'

when token_contract = 'A.0f9df91c9121c460.BloctoToken' then 'BLT'

when token_contract = 'A.231cc0dbbcffc4b7.RLY' then 'RLY'

when token_contract = 'A.475755d2c9dccc3a.TeleportedSportiumToken' then 'SPRT'

when token_contract = 'A.d01e482eb680ec9f.REVV' then 'REVV'

when token_contract = 'A.142fa6570b62fd97.StarlyToken' then 'STARLY'

end as symbol,

token_contract,

blockchain,

flow_wallet_address,

round(amount,2) as total_amount

from flow.core.fact_bridge_transactions where

direction = 'outbound'

),

inflows as (

select date(block_timestamp) as day,

case when token_contract = 'A.231cc0dbbcffc4b7.ceBNB' then 'BNB'

when token_contract = 'A.231cc0dbbcffc4b7.ceWBTC' then 'WBTC'

when token_contract = 'A.231cc0dbbcffc4b7.ceUSDT' then 'USDT'

when token_contract = 'A.231cc0dbbcffc4b7.ceWETH' then 'WETH'