Query editor
-- DeFi20 Full Cross-Sector Leaderboard — 30-day estimated fees ranking-- Phase 3: Uniswap fixed (LIKE + block_date), Jupiter added (jupiterz), Pendle added (RouterV4)WITH uniswap AS (SELECT'Uniswap' AS protocol,'Spot Markets' AS sector,SUM(amount_usd) AS volume_30d,SUM(amount_usd * 0.0025) AS fees_30d,COUNT(DISTINCT CAST(tx_from AS varchar)) AS users_30dFROM dex.tradesWHERE project LIKE 'uniswap%'AND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY)AND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10),curve_aerodrome AS (SELECTCASE WHEN project LIKE 'curve%' THEN 'Curve'WHEN project = 'aerodrome' THEN 'Aerodrome' END AS protocol,'Spot Markets' AS sector,SUM(amount_usd) AS volume_30d,SUM(amount_usd * CASE WHEN project LIKE 'curve%' THEN 0.0004WHEN project = 'aerodrome' THEN 0.002 END) AS fees_30d,COUNT(DISTINCT CAST(tx_from AS varchar)) AS users_30dFROM dex.tradesWHERE project IN ('curve', 'aerodrome')AND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY)AND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10GROUP BY 1, 2),jupiter AS (SELECT'Jupiter' AS protocol,'Spot Markets' AS sector,SUM(amount_usd) AS volume_30d,SUM(COALESCE(fee_usd, amount_usd * 0.0025)) AS fees_30d,COUNT(DISTINCT CAST(trader_id AS varchar)) AS users_30dFROM dex_solana.tradesWHERE project = 'jupiterz'AND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '30' DAY)AND block_date >= CURRENT_DATE - INTERVAL '30' DAYAND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10),lending AS (SELECTCASE WHEN project = 'aave' THEN 'Aave'WHEN project IN ('maker','sky','spark') THEN 'Sky/MakerDAO'WHEN project = 'morpho' THEN 'Morpho'WHEN project = 'euler' THEN 'Euler' END AS protocol,'Lending' AS sector,SUM(amount_usd) AS volume_30d,SUM(amount_usd * 0.003) AS fees_30d,COUNT(DISTINCT CAST(borrower AS varchar)) AS users_30dFROM lending.borrowWHERE project IN ('aave','maker','sky','spark','morpho','euler')