Query editor
WITH swaps AS (SELECTtaker AS trader,tx_hash,amount_usd,CASEWHEN token_bought_address = 0x311935Cd80B76769bF2ecC9D8Ab7635b2139cf82 THEN amount_usdWHEN token_sold_address = 0x311935Cd80B76769bF2ecC9D8Ab7635b2139cf82 THEN -amount_usdEND AS signed_usdFROM dex.tradesWHERE blockchain = 'base'AND project = 'aerodrome'AND (token_bought_address = 0x311935Cd80B76769bF2ecC9D8Ab7635b2139cf82OR token_sold_address = 0x311935Cd80B76769bF2ecC9D8Ab7635b2139cf82)AND amount_usd IS NOT NULL),wallet_agg AS (SELECTtrader,SUM(amount_usd) AS total_volume_usd,SUM(signed_usd) AS net_flow_usd,COUNT(DISTINCT tx_hash) AS transaction_countFROM swapsGROUP BY trader),classification AS (SELECT*,CASE-- Identifying Farmers: High volume (> $100k) and very low net flow (< 1% of volume)WHEN total_volume_usd > 100000AND ABS(net_flow_usd) / total_volume_usd < 0.01THEN 'Farmed (Wash Trading)'ELSE 'Actual Usage (Organic)'END AS volume_typeFROM wallet_agg)SELECTvolume_type,COUNT(DISTINCT trader) AS unique_wallets,SUM(transaction_count) AS total_trades,SUM(total_volume_usd) AS total_usd_volume,-- This gives you the final percentage breakdownROUND(100.0 * SUM(total_volume_usd) / SUM(SUM(total_volume_usd)) OVER (), 2) AS percent_of_total_volumeFROM classificationGROUP BY 1ORDER BY total_usd_volume DESC;