First foray into fact_subscription_events. Practice filtering by event_type and a date range, and using COUNT(DISTINCT) to count distinct entities even though events may have multiple rows per subscription.
Quarterly retention review tomorrow. From fact_subscription_events, count the distinct subscriptions that voluntarily cancelled in Q1 2026 (event_ts between 2026-01-01 and 2026-03-31, event_type = 'cancelled'). One column: cancelled_subs. Don't count churn here — voluntary only. We'll do voluntary-vs-involuntary breakdowns in a future review.
| 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
fact_subscription_events is the canonical event store. Filter event_type = 'cancelled' (voluntary) — 'churned' is involuntary and is a separate cohort.
Date filter on event_ts BETWEEN '2026-01-01' AND '2026-03-31'. event_ts is TEXT formatted YYYY-MM-DD, so string comparison works.
COUNT(DISTINCT subscription_id) is the right counter — a subscription technically can only cancel once (terminal state) but counting distinct guards against any seed anomaly.