Query editor
-- DeFi20 Index — Spot Markets Daily Metrics-- Phase 3 fix: Jupiter project name updated to 'jupiterz' (correct name in dex_solana.trades)WITH evm_daily AS (SELECTblock_date,CASEWHEN project LIKE 'uniswap%' THEN 'Uniswap'WHEN project LIKE 'curve%' THEN 'Curve'WHEN project = 'aerodrome' THEN 'Aerodrome'END AS protocol,SUM(amount_usd) AS volume_usd,SUM(amount_usd * CASEWHEN project LIKE 'uniswap%' THEN 0.0025WHEN project LIKE 'curve%' THEN 0.0004WHEN project = 'aerodrome' THEN 0.002END) AS estimated_fees_usd,COUNT(DISTINCT tx_from) AS active_users,COUNT(*) AS trade_countFROM dex.tradesWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '90' DAY)AND block_date >= CURRENT_DATE - INTERVAL '90' 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),solana_daily AS (SELECTblock_date,'Jupiter' AS protocol,SUM(amount_usd) AS volume_usd,SUM(COALESCE(fee_usd, amount_usd * 0.0025)) AS estimated_fees_usd,COUNT(DISTINCT trader_id) AS active_users,COUNT(*) AS trade_countFROM dex_solana.tradesWHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '90' DAY)AND block_date >= CURRENT_DATE - INTERVAL '90' DAYAND project = 'jupiterz'AND amount_usd IS NOT NULL AND amount_usd > 0 AND amount_usd < 1e10GROUP BY 1, 2),combined AS (SELECT * FROM evm_daily UNION ALL SELECT * FROM solana_daily)SELECTblock_date, protocol, volume_usd, estimated_fees_usd, active_users, trade_count,SUM(volume_usd) OVER (PARTITION BY protocol ORDER BY block_date) AS cumulative_volume,SUM(estimated_fees_usd) OVER (PARTITION BY protocol ORDER BY block_date) AS cumulative_feesFROM combinedORDER BY block_date DESC, protocol