Query editor
WITH base_trades AS (SELECTtaker,token_bought_address,token_sold_address,amount_usd,block_timeFROM dex.tradesWHERE blockchain = 'base'AND block_time > now() - interval '30' day-- Исключаем стейблы и WETHAND token_bought_address NOT IN (0xd9aaecb1de2db01915c05e94c7e75d889308c1d4, 0x50c5725949a651dec450e62030b356c5e00c3b0e, 0x4200000000000000000000000000000000000006)AND token_sold_address NOT IN (0xd9aaecb1de2db01915c05e94c7e75d889308c1d4, 0x50c5725949a651dec450e62030b356c5e00c3b0e, 0x4200000000000000000000000000000000000006)),-- Разделяем сделки на притоки и оттоки USD по каждому токенуtoken_flow AS (SELECTtaker,token_bought_address as token,amount_usd as spent,0 as gained,1 as trade_countFROM base_tradesUNION ALLSELECTtaker,token_sold_address as token,0 as spent,amount_usd as gained,1 as trade_countFROM base_trades),trader_performance AS (SELECTtaker,token,SUM(spent) as total_spent,SUM(gained) as total_gained,SUM(trade_count) as tradesFROM token_flowGROUP BY 1, 2),final_stats AS (SELECTtaker,COUNT(DISTINCT token) as unique_assets,SUM(trades) as total_trades,SUM(total_gained - total_spent) as total_realized_profit_usd,AVG((total_gained - total_spent) / NULLIF(total_spent, 0)) * 100 as avg_roi_percentFROM trader_performanceWHERE total_spent > 0 AND total_gained > 0GROUP BY 1)SELECT