Query editor
WITH life AS (SELECTproject,MIN(day) AS first_seen,MAX(day) AS last_seen,COUNT(*) AS active_days,SUM(CASE WHEN day >= CURRENT_DATE - INTERVAL '365' DAY THEN messages ELSE 0 END) AS msgs_365d,SUM(messages) AS msgs_allFROM dune.gigagobin.result_canary_sentinel_app_dailyWHERE day < CURRENT_DATEGROUP BY 1),ranked AS (SELECT *,ROW_NUMBER() OVER (ORDER BY msgs_365d DESC) AS rank_365d,CASEWHEN active_days < 14 THEN '<14d'WHEN active_days < 60 THEN '14-59d'WHEN active_days < 90 THEN '60-89d'WHEN active_days < 180 THEN '90-179d'ELSE '180d+'END AS history_bucketFROM lifeWHERE msgs_365d > 0)SELECThistory_bucket,COUNT(*) AS apps,SUM(msgs_365d) AS msgs_365d_sum,SUM(CASE WHEN rank_365d <= 25 THEN 1 ELSE 0 END) AS in_top25,SUM(CASE WHEN rank_365d > 25 THEN 1 ELSE 0 END) AS long_tail_apps,approx_percentile(active_days, 0.5) AS active_days_med,approx_percentile(msgs_365d, 0.5) AS msgs_365d_medFROM rankedGROUP BY 1ORDER BY CASE history_bucketWHEN '<14d' THEN 1 WHEN '14-59d' THEN 2 WHEN '60-89d' THEN 3WHEN '90-179d' THEN 4 ELSE 5 END