with price as ( select hour::date as date, symbol, avg(price) as avg_price from flow.price.ez_prices_hourly where symbol is not null and symbol not in ('FLOW','CEWBTC') group by 1,2 UNION select hour::date as date, symbol, avg(price) as avg_price from ethereum.price.ez_prices_hourly where symbol = 'ETH' group by 1,2 UNION select hour::date as date, 'CEWBTC' as symbol, avg(price) as avg_price from ethereum.price.ez_prices_hourly where symbol = 'WBTC' group by 1,2 UNION select hour::date as date, symbol, avg(price) as avg_price from polygon.price.ez_prices_hourly where symbol = 'MATIC' group by 1,2 UNION Select TO_TIMESTAMP(value[0]::string) as date, 'BLT' as symbol, value[1] as avg_price from ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/blocto-token/market_chart?vs_currency=usd&days=365&interval=daily&precision=3') as resp ) ,LATERAL FLATTEN (input => resp:data:prices) UNION Select TO_TIMESTAMP(value[0]::string) as date, 'stFLOW' as symbol, value[1] as avg_price from ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/flow/market_chart?vs_currency=usd&days=365&interval=daily&precision=3') as resp ) ,LATERAL FLATTEN (input => resp:data:prices) UNION Select TO_TIMESTAMP(value[0]::string) as date, 'FLOW' as symbol, value[1] as avg_price from ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/flow/market_chart?vs_currency=usd&days=365&interval=daily&precision=3') as resp ) ,LATERAL FLATTEN (input => resp:data:prices)) , swap as ( select block_timestamp::date as date, tx_id, trader, case when token_in_contract = 'A.3c5959b568896393.FUSD' then 'FUSD' when token_in_contract = 'A.1654653399040a61.FlowToken' then 'FLOW' when token_in_contract = 'A.b19436aae4d94622.FiatToken' then 'USDC' when token_in_contract = 'A.231cc0dbbcffc4b7.ceWBTC' then 'CEWBTC' when token_in_contract = 'A.921ea449dffec68a.FlovatarDustToken' then 'FDUST' when token_in_contract = 'A.231cc0dbbcffc4b7.ceMATIC' then 'MATIC' when token_in_contract = 'A.0f9df91c9121c460.BloctoToken' then 'BLT' when token_in_contract = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then 'USDT' when token_in_contract = 'A.231cc0dbbcffc4b7.ceWETH' then 'ETH' when token_in_contract = 'A.d6f80565193ad727.stFlowToken' then 'stFLOW' else token_in_contract end as token_in, case when token_out_contract = 'A.3c5959b568896393.FUSD' then 'FUSD' when token_out_contract = 'A.1654653399040a61.FlowToken' then 'FLOW' when token_out_contract = 'A.b19436aae4d94622.FiatToken' then 'USDC' when token_out_contract = 'A.231cc0dbbcffc4b7.ceWBTC' then 'CEWBTC' when token_out_contract = 'A.921ea449dffec68a.FlovatarDustToken' then 'FDUST' when token_out_contract = 'A.231cc0dbbcffc4b7.ceMATIC' then 'MATIC' when token_out_contract = 'A.0f9df91c9121c460.BloctoToken' then 'BLT' when token_out_contract = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then 'USDT' when token_out_contract = 'A.231cc0dbbcffc4b7.ceWETH' then 'ETH' when token_out_contract = 'A.d6f80565193ad727.stFlowToken' then 'stFLOW' else token_out_contract end as token_out, token_in_amount, token_out_amount from flow.defi.ez_swaps where block_timestamp::date >= '2024-01-01' ) , swap_volume as ( select a.date, tx_id, trader, token_in, token_out, b.symbol as token_in_name, c.symbol as token_out_name, case when token_in in ('USDT','USDC','FUSD') then token_in_amount else token_in_amount*b.avg_price end as amt_in_usd, case when token_out in ('USDT','USDC','FUSD') then token_out_amount else token_out_amount*c.avg_price end as amt_out_usd, case when amt_in_usd is null then amt_out_usd else amt_in_usd end as volume from swap a left outer join price b on a.token_in = b.symbol and a.date = b.date left outer join price c on a.token_out = c.symbol and a.date = c.date ) , final as (select trader, count(DISTINCT tx_id) as transactions, sum(volume) as usd_volume, avg(volume) as "Avg Volume", median(volume) as "Median Volume", max(volume) as "Max Volume", count(DISTINCT token_in) as traded_token from swap_volume group by 1) select count(DISTINCT trader) as traders, case when usd_volume <= 10 then 'a. < $10' when usd_volume <= 100 then 'b. $10-100' when usd_volume <= 250 then 'c. $100-1250' when usd_volume <= 500 then 'd. $250-500' when usd_volume <= 1000 then 'e. $500-1k' when usd_volume <= 5000 then 'f. $1K-5K' when usd_volume <= 10000 then 'g. $5K-10K' when usd_volume <= 50000 then 'h. $10K-50K' when usd_volume <= 100000 then 'i. $50K-100K' when usd_volume > 100000 then 'j. > $100K' end as category from final group by 2

Trading on Flow Chain!