First contact with fact_subscription_snapshots — the most-queried table in subscription analytics. Practice single-table SELECT, WHERE filter on a fixed date, GROUP BY, COUNT(*), and a two-level ORDER BY (descending on the metric, ascending on the dimension as a stable tiebreaker).
Welcome to the data team. Before we touch any dashboard, get familiar with our snapshot table — fact_subscription_snapshots is the source of truth for current-state subscription analytics. From snapshot_date '2026-03-31' (the latest day in our window), give me a count of subscriptions per plan_id. Two columns: plan_id, sub_count. Sort by sub_count descending so the largest plan leads; tiebreaker plan_id ascending so the order is stable when plans tie.
| 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.
Single-table query. fact_subscription_snapshots is the only table you need — filter to snapshot_date = '2026-03-31', group by plan_id, COUNT(*).
The snapshot table contains both 'active' and 'paused' subscriptions on each date. We want both — no state filter for this one.
Two-level ORDER BY keeps the output stable when two plans have the same sub_count: descending on sub_count, ascending on plan_id.