Query editor
SELECT week,CASEWHEN frequency < 10 THEN 'Low(< 10)'WHEN frequency >= 10 AND frequency < 100 THEN 'Medium(10-100)'ELSE 'High(>=100)'END AS frequency,SUM(volume) as volumeFROM (SELECT date_trunc ('week', trade_time) as week, trader, sum(volume * price) as volume, count(*) as frequencyFrom(SELECTt.taker as trader,t.evt_block_time as trade_time,(t.price / 1e18) * ABS(t.takeSize / 1e18) AS volume,p.price as priceFROMsynfutures_v2_polygon.Underlying_evt_Trade tLEFT JOIN synfutures_v2_polygon.Router_evt_NewUnderlying u ON t.contract_address = u.underlyingLEFT JOIN prices.usd p ON p.contract_address = u.quoteAND Date(t.evt_block_time) = p.minuteAND p.blockchain = 'polygon')Group by 1,2)Group by 1,2-- with week_volume as (-- SELECT date_trunc ('week', trade_time) as week, trader, sum(volume * price) as volume, count(*) as frequency-- From(-- SELECT-- t.trader as trader,-- t.evt_block_time as trade_time,-- (t.price / 1e18) * ABS(t.takeSize / 1e18) AS volume,-- p.price as price-- FROM-- synfutures_v2_polygon.Underlying_evt_Trade t-- LEFT JOIN synfutures_v2_polygon.Router_evt_NewUnderlying u ON t.contract_address = u.underlying-- LEFT JOIN prices.usd p ON p.contract_address = u.quote-- AND Date(t.evt_block_time) = p.minute-- AND p.blockchain = 'polygon'-- )-- Group by 1,2-- )-- CASE-- WHEN total_transfers >= 100 THEN 'Frequent'-- WHEN total_transfers >= 10 AND total_transfers < 100 THEN 'Moderate'-- ELSE 'Infrequent'-- END AS user_type-- week_volume_rank as (-- select week, trader, volume, RANK() OVER(partition by week order by volume desc) as volume_rank from week_volume-- )-- select-- week,-- total_volume,