First INNER JOIN. Practice joining a fact (snapshots) to a dim (customers) on customer_id, filtering on two columns at once (snapshot_date AND state), and counting distinct values inside an aggregation.
International expansion review with the CEO at 2 PM. From the latest snapshot ('2026-03-31'), I need the count of active subscribers grouped by country. We only want state = 'active' — paused subs aren't paying right now and the CEO will conflate them with churned. Two columns: country, active_count. Sort active_count descending, country ascending as tiebreaker.
| 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 |
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| TEXT | ||
| first_name | TEXT | |
| last_name | TEXT | |
| country | TEXT | |
| signup_date | TEXT | |
| acquisition_channel | TEXT | |
| is_subscriber | INT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Snapshot filter plus a state filter — fact_subscription_snapshots has rows for both 'active' and 'paused' subs each day; we only want 'active' here.
JOIN dim_customers on customer_id to pull the country column. INNER JOIN is fine — every snapshot row has a valid customer.
COUNT(DISTINCT subscription_id) is safer than COUNT(*) — even though one subscription is one row per snapshot date, defensive analysts always count distinct on the entity grain.