Query editor
WITH spot_evm AS (SELECT CASE WHEN project LIKE 'uniswap%' THEN 'Uniswap' 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 'uniswap%' THEN 0.0025 WHEN project LIKE 'curve%' THEN 0.0004 WHEN project = 'aerodrome' THEN 0.002 END) AS fees_30d,COUNT(DISTINCT tx_from) AS active_users_30dFROM 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 < 1e10GROUP BY 1, 2),spot_solana 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 trader_id) AS active_users_30dFROM 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 < 1e10GROUP BY 1, 2),lending_protocols AS (SELECT CASE WHEN project LIKE 'aave%' THEN 'Aave' WHEN project LIKE 'morpho%' THEN 'Morpho' WHEN project LIKE 'euler%' THEN 'Euler'WHEN project LIKE 'spark%' OR project LIKE 'maker%' OR project LIKE 'sky%' THEN 'Sky / MakerDAO' END AS protocol,'Lending' AS sector, SUM(amount_usd) AS volume_30d, SUM(amount_usd) * 0.003 AS fees_30d,COUNT(DISTINCT borrower) AS active_users_30dFROM lending.borrowWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '60' DAY)AND block_time >= CURRENT_DATE - INTERVAL '30' DAYAND (project LIKE 'aave%' OR project LIKE 'morpho%' OR project LIKE 'euler%' OR project LIKE 'spark%' OR project LIKE 'maker%' OR project LIKE 'sky%')AND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10GROUP BY 1, 2),all_protocols AS (SELECT * FROM spot_evm UNION ALL SELECT * FROM spot_solana UNION ALL SELECT * FROM lending_protocols)SELECT ROW_NUMBER() OVER (ORDER BY fees_30d DESC) AS rank, protocol, sector, fees_30d, volume_30d, active_users_30d,fees_30d / NULLIF(volume_30d, 0) AS fee_rateFROM all_protocols WHERE protocol IS NOT NULL ORDER BY fees_30d DESC