Query editor
WITH top25 AS (SELECT projectFROM dune.gigagobin.result_canary_sentinel_app_dailyWHERE day >= CURRENT_DATE - INTERVAL '365' DAY AND day < CURRENT_DATEGROUP BY 1ORDER BY SUM(messages) DESCLIMIT 25),d AS (SELECT *FROM dune.gigagobin.result_canary_sentinel_app_dailyWHERE day < CURRENT_DATEAND project IN (SELECT project FROM top25))SELECTproject,COUNT(*) AS active_days,SUM(messages) AS msgs_all,SUM(volume_usd) AS usd_all,COUNT_IF(volume_usd = 0) AS days_usd_zero,COUNT_IF(volume_usd > 0) AS days_usd_positive,COUNT_IF(volume_usd = 0) * 1.0 / COUNT(*) AS share_days_usd_zero,COUNT_IF(messages > 0 AND volume_usd = 0) AS days_msgs_but_usd_zero,approx_percentile(CASE WHEN volume_usd > 0 THEN avg_usd_per_message END, 0.5) AS ticket_median_valued_days,SUM(CASE WHEN volume_usd > 0 THEN messages ELSE 0 END) * 1.0 / NULLIF(SUM(messages), 0) AS share_msgs_on_valued_daysFROM dGROUP BY 1ORDER BY msgs_all DESC