Query editor
WITH evm_trades AS (SELECTCASE WHEN project LIKE 'uniswap%' THEN 'Uniswap' WHEN project LIKE 'curve%' THEN 'Curve' WHEN project = 'aerodrome' THEN 'Aerodrome' END AS protocol,amount_usd,amount_usd * CASE WHEN project LIKE 'uniswap%' THEN 0.0025 WHEN project LIKE 'curve%' THEN 0.0004 WHEN project = 'aerodrome' THEN 0.002 END AS fee_usd,CAST(tx_from AS varchar) AS user_addrFROM dex.tradesWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '60' DAY)AND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND (project LIKE 'uniswap%' OR project LIKE 'curve%' OR project = 'aerodrome')AND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10),solana_trades AS (SELECT 'Jupiter' AS protocol, amount_usd, COALESCE(fee_usd, amount_usd * 0.0025) AS fee_usd, trader_id AS user_addrFROM dex_solana.tradesWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '60' DAY)AND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND project = 'jupiter'AND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10),all_trades AS (SELECT * FROM evm_trades UNION ALL SELECT * FROM solana_trades),kpis AS (SELECT protocol, SUM(amount_usd) AS total_volume_30d, SUM(fee_usd) AS total_fees_30d,COUNT(DISTINCT user_addr) AS unique_users_30d, COUNT(*) AS total_trades_30dFROM all_trades GROUP BY 1),sector_total AS (SELECT 'Spot Markets (Total)' AS protocol, SUM(total_volume_30d), SUM(total_fees_30d), SUM(unique_users_30d), SUM(total_trades_30d) FROM kpis)SELECT * FROM kpis UNION ALL SELECT * FROM sector_total ORDER BY total_fees_30d DESC