Query editor
WITH apps AS (SELECT * FROM (VALUES ('Ethena'), ('PayPal'), ('Ether.Fi'), ('Stargate')) AS t(project)),daily 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_DATEAND d.day >= CURRENT_DATE - INTERVAL '365' DAY),meds AS (SELECT project,approx_percentile(volume_usd, 0.5) AS usd_med,approx_percentile(messages, 0.5) AS msgs_med,approx_percentile(volume_usd, 0.75) AS usd_p75FROM daily GROUP BY 1),usd_ctrl AS (SELECT d.project, d.day, d.messages, d.volume_usd, 'usd_near_med' AS role,ROW_NUMBER() OVER (PARTITION BY d.project ORDER BY ABS(d.volume_usd - m.usd_med)) AS rnFROM daily d INNER JOIN meds m ON d.project = m.project),msgs_ctrl AS (SELECT d.project, d.day, d.messages, d.volume_usd, 'msgs_near_med' AS role,ROW_NUMBER() OVER (PARTITION BY d.project ORDER BY ABS(CAST(d.messages AS double) - CAST(m.msgs_med AS double))) AS rnFROM daily d INNER JOIN meds m ON d.project = m.project),usd_p75_ctrl AS (SELECT d.project, d.day, d.messages, d.volume_usd, 'usd_near_p75' AS role,ROW_NUMBER() OVER (PARTITION BY d.project ORDER BY ABS(d.volume_usd - m.usd_p75)) AS rnFROM daily d INNER JOIN meds m ON d.project = m.project),efi AS (SELECT volume_usd AS val FROM daily WHERE project = 'Ether.Fi'),efi_med AS (SELECT approx_percentile(val, 0.5) AS med FROM efi),efi_mad AS (SELECT approx_percentile(ABS(e.val - m.med), 0.5) AS mad, m.medFROM efi e CROSS JOIN efi_med m GROUP BY m.med),efi_msgs AS (SELECT CAST(messages AS double) AS val FROM daily WHERE project = 'Ether.Fi'),efi_msgs_med AS (SELECT approx_percentile(val, 0.5) AS med FROM efi_msgs),efi_msgs_mad AS (SELECT approx_percentile(ABS(e.val - m.med), 0.5) AS mad, m.medFROM efi_msgs e CROSS JOIN efi_msgs_med m GROUP BY m.med)SELECT project, day, messages, volume_usd, role FROM usd_ctrl WHERE rn = 1UNION ALLSELECT project, day, messages, volume_usd, role FROM msgs_ctrl WHERE rn = 1UNION ALLSELECT project, day, messages, volume_usd, role FROM usd_p75_ctrl WHERE rn = 1UNION ALLSELECT 'Ether.Fi' AS project, CAST(NULL AS date) AS day, CAST(NULL AS bigint) AS messages,med + 3 * mad AS volume_usd, 'mad_k3_usd_upper' AS role FROM efi_madUNION ALLSELECT 'Ether.Fi', CAST(NULL AS date), CAST(ROUND(med) AS bigint),med + 3 * mad, 'mad_k3_msgs_upper' FROM efi_msgs_madORDER BY role, project