Query editor
-- P5 MAD band-edge receipt vs k_ex (extends 8654698)-- Apps: Ethena, PayPal, Stargate | windows 90d/365d | metrics USD + msgs-- band_kind: inclusive | ex_k3 | ex_k5 | mad_k2 | mad_k3WITH apps AS (SELECT * FROM (VALUES ('Ethena'), ('PayPal'), ('Stargate')) 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 ('inclusive'), ('ex_k3'), ('ex_k5')) AS t(variant)),usd_rows AS (SELECT r.project, r.w, v.variant, r.volume_usd AS valFROM rnk r CROSS JOIN variants vWHERE (v.variant = 'inclusive')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 = 'inclusive')OR (v.variant = 'ex_k3' AND r.rn_msgs > 3)OR (v.variant = 'ex_k5' AND r.rn_msgs > 5)),kex_usd AS (SELECT project, w, variant AS band_kind, 'usd' AS metric,COUNT(*) AS n,approx_percentile(val, 0.5) AS med,approx_percentile(val, 0.9) AS p90_or_upper,CAST(NULL AS double) AS mad,CAST(NULL AS double) AS kappaFROM usd_rows GROUP BY 1,2,3),kex_msgs AS (SELECT project, w, variant AS band_kind, 'msgs' AS metric,COUNT(*) AS n,approx_percentile(val, 0.5) AS med,approx_percentile(val, 0.9) AS p90_or_upper,CAST(NULL AS double) AS mad,CAST(NULL AS double) AS kappa