select symbol, avg(price_usd) as price

from flow.core.fact_prices

where timestamp::date = '2022-06-29'

group by 1

),

price_eth as (

select symbol, avg(price) as price

from ethereum.core.fact_hourly_token_prices

where hour::date = current_date - 1 and symbol in ('FTM', 'USDT', 'WETH', 'WBTC', 'BUSD', 'MATIC')

group by 1

),

price_avax_bnb as (

select case

when token_address = '0x85f138bfee4ef8e540890cfb48f620571d67eda3' then 'AVAX'

else 'BNB' end as symbol, avg(price) as price

from ethereum.core.fact_hourly_token_prices

where hour::date = current_date - 1 and token_address in ('0x85f138bfee4ef8e540890cfb48f620571d67eda3', '0x418d75f65a02b3d53b2418fb8e1fe493759c7605')

group by 1

select date_trunc('day', block_timestamp) as date, bridge, case

when split_part(token_contract, '.', 3) = 'FlowToken' then 'FLOW'

when split_part(token_contract, '.', 3) = 'RLY' then 'RLY'

when split_part(token_contract, '.', 3) = 'StarlyToken' then 'STARLY'

when split_part(token_contract, '.', 3) = 'REVV' then 'REVV'

when split_part(token_contract, '.', 3) = 'BloctoToken' then 'BLT'

when split_part(token_contract, '.', 3) = 'TeleportedSportiumToken' then 'SPRT'

end as tokens, sum(amount * price) as usd_volume

from flow.core.fact_bridge_transactions, price_flow

where tokens = symbol and direction = 'outbound'

group by 1, 2, 3

union all