Query editor
-- OP-Stack DEX Landscape · Ink USD share — 7d trailing (WIDE-PIVOTED)WITH ink_trades AS (SELECT DATE_TRUNC('day', block_time) AS day, project, version, amount_usdFROM dex.tradesWHERE blockchain = 'ink'AND block_time >= TIMESTAMP '2024-12-20 00:00:00'AND block_month >= DATE '2024-12-01'AND project NOT IN ('synthetix', 'bridgers')AND amount_usd IS NOT NULL AND amount_usd > 0),labeled AS (SELECT day, amount_usd,CASEWHEN project = 'velodrome' THEN 'Velodrome'WHEN project = 'uniswap' THEN 'Uniswap'WHEN (project, version) = ('inkyswap', '1') THEN 'Inkyswap'WHEN (project, version) = ('reservoir_swap', '1') THEN 'Reservoir'WHEN (project, version) = ('inkswap', '1') THEN 'Inkswap'ELSE INITCAP(project)END AS display_nameFROM ink_trades),daily_legend AS (SELECT day,CASE display_nameWHEN 'Velodrome' THEN 'Velodrome'WHEN 'Inkyswap' THEN 'Inkyswap'WHEN 'Uniswap' THEN 'Uniswap'WHEN 'Reservoir' THEN 'Reservoir'ELSE 'Other'END AS legend_name,amount_usdFROM labeled),daily_usd AS (SELECT day, legend_name, SUM(amount_usd) AS usd_volume FROM daily_legend GROUP BY day, legend_name),rolling_legend AS (SELECT day, legend_name,SUM(usd_volume) OVER (PARTITION BY legend_name ORDER BY dayRANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW) AS usd_volume_7dFROM daily_usd),rolling_total AS (SELECT day AS total_day, SUM(usd_volume_7d) AS day_total_7d FROM rolling_legend GROUP BY day),shares AS (SELECT r.day AS day, r.legend_name AS display_name,100.0 * r.usd_volume_7d / NULLIF(t.day_total_7d, 0) AS share_pct_7dFROM rolling_legend r JOIN rolling_total t ON r.day = t.total_dayWHERE r.day >= DATE '2024-12-26')SELECT day,COALESCE(MAX(CASE WHEN display_name = 'Velodrome' THEN share_pct_7d END), 0) AS velodrome,COALESCE(MAX(CASE WHEN display_name = 'Inkyswap' THEN share_pct_7d END), 0) AS inkyswap,COALESCE(MAX(CASE WHEN display_name = 'Uniswap' THEN share_pct_7d END), 0) AS uniswap,COALESCE(MAX(CASE WHEN display_name = 'Reservoir' THEN share_pct_7d END), 0) AS reservoir,COALESCE(MAX(CASE WHEN display_name = 'Other' THEN share_pct_7d END), 0) AS otherFROM shares GROUP BY day ORDER BY day