Query editor
WITH apps AS (SELECT * FROM (VALUES ('Ethena'), ('PayPal'), ('Stargate'), ('Ether.Fi'), ('USDT0')) AS t(project)),base AS (SELECT d.project, d.day, d.messages, d.volume_usdFROM dune.gigagobin.result_canary_sentinel_app_daily dINNER JOIN apps a ON d.project = a.projectWHERE d.day < CURRENT_DATE AND d.day >= CURRENT_DATE - INTERVAL '365' DAY),wins AS (SELECT * FROM (VALUES ('90d'), ('365d')) AS t(w)),expanded AS (SELECT b.project, w.w, b.day, b.messages, b.volume_usdFROM base bCROSS JOIN wins wWHERE (w.w = '365d') OR (w.w = '90d' AND b.day >= CURRENT_DATE - INTERVAL '90' DAY)),rnk AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY project, w ORDER BY volume_usd DESC) AS rn_usd,ROW_NUMBER() OVER (PARTITION BY project, w ORDER BY messages DESC) AS rn_msgsFROM expanded),variants AS (SELECT * FROM (VALUES ('all'), ('ex_k1'), ('ex_k3'), ('ex_k5')) AS t(variant)),filtered AS (SELECT r.project, r.w, v.variant, r.messages, r.volume_usdFROM rnk rCROSS JOIN variants vWHERE (v.variant = 'all')OR (v.variant = 'ex_k1' AND r.rn_usd > 1 AND r.rn_msgs > 0))-- simpler: separate usd and msgs filters, usd_rows AS (SELECT r.project, r.w, v.variant, r.volume_usd AS valFROM rnk r CROSS JOIN variants vWHERE (v.variant = 'all')OR (v.variant = 'ex_k1' AND r.rn_usd > 1)OR (v.variant = 'ex_k3' AND r.rn_usd > 3)OR (v.variant = 'ex_k5' AND r.rn_usd > 5)),msgs_rows AS (SELECT r.project, r.w, v.variant, CAST(r.messages AS double) AS valFROM rnk r CROSS JOIN variants vWHERE (v.variant = 'all')OR (v.variant = 'ex_k1' AND r.rn_msgs > 1)OR (v.variant = 'ex_k3' AND r.rn_msgs > 3)OR (v.variant = 'ex_k5' AND r.rn_msgs > 5)),usd_stats AS (SELECT project, w, variant, 'usd' AS metric,COUNT(*) AS n,approx_percentile(val, 0.5) AS med,approx_percentile(val, 0.9) AS p90FROM usd_rows GROUP BY 1,2,3),msgs_stats AS (SELECT project, w, variant, 'msgs' AS metric,