from flow.core.fact_prices

group by 1,2

,

native as ( select date(BLOCK_TIMESTAMP) as date , case when TOKEN_CONTRACT = 'A.1654653399040a61.FlowToken' then 'FLOW'

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

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.142fa6570b62fd97.StarlyToken' then 'STARLY' end as token , tx_id , BRIDGE ,FLOW_WALLET_ADDRESS, BLOCKCHAIN , DIRECTION , AMOUNT

from flow.core.fact_bridge_transactions

,

non_native as ( select date(BLOCK_TIMESTAMP) as date, case when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceFTM' then 'FTM'

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

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceBUSD' then 'BUSD'

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

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

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceBNB' then 'sBNB'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceAVAX' then 'WAVAX'

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

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceMATIC' then 'MATIC' end as token , tx_id ,FLOW_WALLET_ADDRESS, BRIDGE , BLOCKCHAIN , DIRECTION , AMOUNT

from flow.core.fact_bridge_transactions

,

non_price as ( select date(hour) as n_date , symbol , avg(price) as n_price

from ethereum.core.fact_hourly_token_prices

where symbol in ( select token from non_native) and hour::date >= '2022-05-01'

group by 1,2

)

,

tables as ( select date , token , tx_id , bridge, FLOW_WALLET_ADDRESS,direction , amount, amount*prices as volume

from price a left outer join native b on a.p_date = b.date

where token is not null