Query editor
WITH apps AS (SELECT * FROM (VALUES('Ethena'), ('PayPal'), ('Ether.Fi'), ('Stargate'), ('USDT0'), ('aPriori')) AS t(project)),daily AS (SELECT d.*FROM 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),ranked AS (SELECTproject, day, messages, volume_usd,ROW_NUMBER() OVER (PARTITION BY project ORDER BY messages DESC) AS rn_msgs,ROW_NUMBER() OVER (PARTITION BY project ORDER BY volume_usd DESC) AS rn_usdFROM daily),base_stats AS (SELECTproject,'all_days' AS variant,'365d' AS window_name,COUNT(*) AS n_days,approx_percentile(messages, 0.5) AS msgs_med,approx_percentile(messages, 0.9) AS msgs_p90,approx_percentile(volume_usd, 0.5) AS usd_med,approx_percentile(volume_usd, 0.9) AS usd_p90,MAX(messages) AS msgs_max,MAX(volume_usd) AS usd_maxFROM dailyGROUP BY 1UNION ALLSELECTproject,'ex_top3_msgs','365d',COUNT(*),approx_percentile(messages, 0.5),approx_percentile(messages, 0.9),approx_percentile(volume_usd, 0.5),approx_percentile(volume_usd, 0.9),MAX(messages),MAX(volume_usd)FROM rankedWHERE rn_msgs > 3GROUP BY 1UNION ALLSELECTproject,'ex_top3_usd','365d',COUNT(*),approx_percentile(messages, 0.5),approx_percentile(messages, 0.9),approx_percentile(volume_usd, 0.5),approx_percentile(volume_usd, 0.9),MAX(messages),MAX(volume_usd)