with tb1 as ( select trunc(block_timestamp,'day') as day, tx_id, AMOUNT, amount_usd, ifnull(destination_address,source_address) as user, source_chain, case when token_address = 'A.231cc0dbbcffc4b7.ceWBTC' then 'WBTC' when token_address = 'A.231cc0dbbcffc4b7.RLY' then 'RLY' when token_address = 'A.231cc0dbbcffc4b7.ceFTM' then 'FTM' when token_address = 'A.475755d2c9dccc3a.TeleportedSportiumToken' then 'SPORTIUM' when token_address = 'A.0f9df91c9121c460.BloctoToken' then 'BLOCKTO' when token_address = 'A.231cc0dbbcffc4b7.ceAVAX' then 'WAVAX' when token_address = 'A.231cc0dbbcffc4b7.ceBUSD' then 'BUSD' when token_address = 'A.231cc0dbbcffc4b7.ceWETH' then 'ETH' when token_address = 'A.231cc0dbbcffc4b7.ceMATIC' then 'WMATIC' when token_address = 'A.1654653399040a61.FlowToken' then 'FLOW' when token_address = 'A.142fa6570b62fd97.StarlyToken' then 'STARLY' when token_address = 'A.231cc0dbbcffc4b7.ceUSDT' then 'USDT' when token_address = 'A.d01e482eb680ec9f.REVV' then 'REVV' when token_address = 'A.b19436aae4d94622.FiatToken' then 'USDC' when token_address = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then 'USDT' when token_address = 'A.231cc0dbbcffc4b7.ceBNB' then 'WBNB' when token_address = 'A.231cc0dbbcffc4b7.ceDAI' then 'DAI' when token_address = 'A.231cc0dbbcffc4b7.ceWBTC' then 'WBTC' when token_address = 'A.9c6f94adf47904b5.UsdcUsdtSwapPair' then 'USDC' end as token_name, token_address from flow.defi.ez_bridge_activity where destination_chain in ('FLOW','flow','Flow') ) , tb2 as ( select trunc(hour,'day') as day, 'FLOW' as token_symbol, avg(price) as token_price from flow.price.ez_prices_hourly where symbol = 'FLOW' group by 1,2 UNION select trunc(hour,'day') as day, symbol as token_symbol, avg(price) as token_price from ethereum.price.ez_prices_hourly where symbol in ('ETH','WBNB','WAVAX','WBTC','WMATIC','FTM') group by 1,2 UNION select trunc(hour,'day') as day, 'BLOCKTO' as token_symbol, avg(price) as token_price from solana.price.ez_prices_hourly where token_address = 'BLT1noyNr3GttckEVrtcfC6oyK6yV1DpPgSyXbncMwef' group by 1,2 UNION select trunc(hour,'day') as day, 'STARLY' as token_symbol, avg(price) as token_price from bsc.price.ez_prices_hourly where token_address = '0xb0a480e2fa5af51c733a0af9fcb4de62bc48c38b' group by 1,2 UNION select trunc(hour,'day') as day, 'RLY' as token_symbol, avg(price) as token_price from ethereum.price.ez_prices_hourly where token_address = '0xf1f955016ecbcd7321c7266bccfb96c68ea5e49b' group by 1,2 UNION select trunc(hour,'day') as day, 'REVV' as token_symbol, avg(price) as token_price from ethereum.price.ez_prices_hourly where token_address = '0x557b933a7c2c45672b610f8954a3deb39a51a8ca' group by 1,2 UNION Select TO_TIMESTAMP(value[0]::string) as day, 'SPORTIUM' as token_symbol, value[1] as token_price from ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/sportium/market_chart?vs_currency=usd&days=365&interval=daily&precision=3') as resp ) ,LATERAL FLATTEN (input => resp:data:prices)) , tb3 as ( select a.day, tx_id, user, source_chain, amount, amount_usd, token_address, token_name, case when token_name in ('BUSD','USDT','USDC','DAI') then amount else amount*token_price end as volume_usd, case when volume_usd is null then amount_usd else volume_usd end as amt_usd from tb1 a left outer join tb2 b on a.token_name = b.token_symbol and a.day = b.day ) , tb4 as ( select user, sum(amt_usd) as volume from tb3 group by 1) select count(DISTINCT user) as users, case when volume <= 10 then '<= 10 USD' when volume <= 50 then '10-50 USD' when volume <= 100 then '50-100 USD' when volume <= 500 then '100-500 USD' when volume <= 1000 then '500-1k USD' when volume <= 10000 then '1k-10k USD' when volume <= 50000 then '10k-50k USD' when volume <= 100000 then '50k-100K USD' when volume <= 1000000 then '100k-1m USD' when volume > 1000000 then '> 1m USD' end as breakdown from tb4 where volume is not null group by 2 order by 1 desc

Bridge to Flow Chain!