First conditional aggregation. Practice SUM(CASE WHEN ...) per category, computing a ratio between two conditional sums, and using NULLIF as a divide-by-zero guard. The skip rate is a key replenishment-brand health metric.
Replenishment health review — I need the trailing-90-day skip rate by plan family. Skip rate = skipped events / (skipped + renewed events) per family, in the window 2026-01-01 to 2026-03-31. Four columns: plan_family, skipped, renewed, skip_rate_pct (rounded to 1 decimal). Sort skip_rate_pct descending, plan_family ascending. Skip rate above 12% is the early warning that customers are stockpiling product or losing engagement — Recovery Reds is the family I'm most worried about.
| Column | Type | Key |
|---|---|---|
| event_id | INT | PK |
| subscription_id | INT | FK → fact_subscriptions |
| customer_id | INT | FK → dim_customers |
| event_type | TEXT | |
| event_ts | TEXT | |
| posted_at | TEXT | |
| plan_id | INT | FK → dim_plans |
| from_plan_id | INT | FK → dim_plans |
| to_plan_id | INT | FK → dim_plans |
| mrr_delta | REAL | |
| reason_code | TEXT | |
| paused_until | TEXT |
| Column | Type | Key |
|---|---|---|
| plan_id | INT | PK |
| plan_family | TEXT | |
| cadence | TEXT | |
| monthly_price | REAL | |
| display_name | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Conditional aggregation: SUM(CASE WHEN event_type = 'skipped' THEN 1 ELSE 0 END) AS skipped, similar for renewed.
Filter event_ts BETWEEN '2026-01-01' AND '2026-03-31' AND event_type IN ('skipped', 'renewed') in the WHERE — narrowing in WHERE is faster than CASE-only filtering.
JOIN dim_plans on plan_id to get plan_family.
skip_rate_pct = ROUND(100.0 * skipped / NULLIF(skipped + renewed, 0), 1). The NULLIF avoids div-by-zero on a hypothetical empty plan family.