Query editor
WITH trades AS (SELECTtaker AS trader,blockchain AS chain,token_bought_symbol AS token,token_bought_amount AS amount,amount_usd,amount_usd / NULLIF(token_bought_amount, 0) AS price_per_token,'buy' AS side,block_timeFROM dex.tradesWHERE blockchain IN ('ethereum','base','arbitrum','solana','optimism','polygon','avalanche_c','bnb')AND block_time >= NOW() - INTERVAL '30' DAYAND amount_usd > 100AND token_bought_amount > 0UNION ALLSELECTtaker AS trader,blockchain AS chain,token_sold_symbol AS token,token_sold_amount AS amount,amount_usd,amount_usd / NULLIF(token_sold_amount, 0) AS price_per_token,'sell' AS side,block_timeFROM dex.tradesWHERE blockchain IN ('ethereum','base','arbitrum','solana','optimism','polygon','avalanche_c','bnb')AND block_time >= NOW() - INTERVAL '30' DAYAND amount_usd > 100AND token_sold_amount > 0),token_summary AS (SELECTtrader, chain, token,SUM(CASE WHEN side='buy' THEN amount_usd ELSE 0 END) AS buy_usd,SUM(CASE WHEN side='sell' THEN amount_usd ELSE 0 END) AS sell_usd,SUM(CASE WHEN side='buy' THEN 1 ELSE 0 END) AS buy_count,SUM(CASE WHEN side='sell' THEN 1 ELSE 0 END) AS sell_countFROM tradesWHERE token NOT IN ('USDC','USDT','DAI','WETH','WBTC','WBNB','WMATIC','WAVAX','WSOL','WFTM')GROUP BY trader, chain, tokenHAVING SUM(CASE WHEN side='buy' THEN 1 ELSE 0 END) > 0AND SUM(CASE WHEN side='sell' THEN 1 ELSE 0 END) > 0),per_wallet AS (SELECTtrader, chain,SUM(sell_usd - buy_usd) AS total_pnl_usd,SUM(buy_usd) AS total_cost_usd,CASE WHEN SUM(buy_usd) > 0THEN SUM(sell_usd - buy_usd) / SUM(buy_usd) * 100ELSE 0 END AS roi_pct,SUM(buy_count + sell_count) AS trade_count,COUNT(DISTINCT token) AS distinct_tokens,SUM(CASE WHEN sell_usd > buy_usd THEN 1 ELSE 0 END) AS winning_tokens,