First DATE_TRUNC. Practice rolling daily snapshots up to a monthly granularity, COUNT(DISTINCT) across many snapshot rows per subscription, and producing a time-series the BI team can chart directly.
Mira's deck for the leadership offsite needs a monthly active subscriber trend. From fact_subscription_snapshots, group by month (DATE_TRUNC the snapshot_date) and count distinct subscriptions where state = 'active'. Two columns: month, active_subs. Sort month ascending. Snapshot window is ~7 months (2025-09-13 → 2026-03-31), so expect 7 month buckets — the first one will be a partial month.
| Column | Type | Key |
|---|---|---|
| snapshot_date | TEXT | PK |
| subscription_id | INT | PKFK → fact_subscriptions |
| customer_id | INT | FK → dim_customers |
| state | TEXT | |
| plan_id | INT | FK → dim_plans |
| mrr | REAL | |
| paused_until | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
DATE_TRUNC('month', snapshot_date::date) returns the first of the month as a date. snapshot_date is TEXT — cast it to date first.
COUNT(DISTINCT subscription_id) is essential here. Without DISTINCT you'd be counting subscription-day pairs across the month (~30x the actual sub count).
GROUP BY DATE_TRUNC('month', snapshot_date::date) and ORDER BY the same expression. Or alias the trunc and order by the alias.