Query editor
WITH src AS (SELECTblock_time,token_bought_symbol,token_sold_symbol,token_bought_mint_address,token_sold_mint_address,amount_usdFROM dex_solana.tradesWHERE block_time >= DATE_TRUNC('day', NOW()) - INTERVAL '30' DAYAND amount_usd IS NOT NULLAND is_finite(amount_usd)AND NOT is_nan(amount_usd)AND amount_usd > 0),sides AS (SELECTDATE_TRUNC('day', block_time) AS day,CASEWHEN UPPER(REPLACE(COALESCE(NULLIF(token_bought_symbol, ''), token_bought_mint_address), '$','')) IN ('WSOL','SOL') THEN 'SOL'ELSE UPPER(REPLACE(COALESCE(NULLIF(token_bought_symbol, ''), token_bought_mint_address), '$',''))END AS token,0.5 * amount_usd AS volFROM srcUNION ALLSELECTDATE_TRUNC('day', block_time) AS day,CASEWHEN UPPER(REPLACE(COALESCE(NULLIF(token_sold_symbol, ''), token_sold_mint_address), '$','')) IN ('WSOL','SOL') THEN 'SOL'ELSE UPPER(REPLACE(COALESCE(NULLIF(token_sold_symbol, ''), token_sold_mint_address), '$',''))END AS token,0.5 * amount_usd AS volFROM src),clean AS (SELECT day, token, volFROM sidesWHERE token IS NOT NULLAND token <> ''AND token NOT LIKE '%TEST%'),agg AS (SELECT CAST(day AS DATE) AS date, token, SUM(vol) AS volume_usdFROM cleanGROUP BY 1, 2),ranked AS (SELECTdate,token,volume_usd,ROW_NUMBER() OVER (PARTITION BY date ORDER BY volume_usd DESC) AS rnFROM agg),top_other AS (SELECTdate,CASE WHEN rn <= 10 THEN token ELSE 'other' END AS token,volume_usdFROM ranked