Date-range filter (BETWEEN) on a single dim table. Practice GROUP BY a low-cardinality column, COUNT(*), and producing a quarterly cohort breakdown the marketing team can paste into a planning doc.
Lifecycle planning for the 2024-Q3 cohort. From dim_customers, count subscribers who signed up between 2024-07-01 and 2024-09-30, broken down by acquisition_channel. is_subscriber = 1 only — we don't want one-time-only buyers polluting the cohort sizing. Two columns: acquisition_channel, new_subs. Sort new_subs descending, acquisition_channel ascending as tiebreaker.
| 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.
Single-table query — dim_customers is the only one you need.
Two filters: is_subscriber = 1 AND signup_date between '2024-07-01' and '2024-09-30'. Both date strings are inclusive (BETWEEN is inclusive in standard SQL).
GROUP BY acquisition_channel, COUNT(*) — six channels exist (organic, paid_search, paid_social, influencer, referral, email).