What Does Our Plan Mix Look Like?
Loopwire's finance lead is weighing up whether to retire the cheapest tier, and before that conversation she wants to see how the tiers compare on take-up across the company's whole history.
History is what the table holds. Cancelled subscriptions are kept as rows rather than deleted, and this question wants all of them — a tier that sold well two years ago and has been churning since is exactly the thing she is trying to spot, so cancellations must not be excluded.
subscriptions — one row per subscription ever sold. plan is the tier. mrr is the monthly fee in dollars. end_date is the day it was cancelled, or NULL if it is still running.
| id | account_id | plan | mrr | start_date | end_date |
|---|---|---|---|---|---|
| 1 | 1 | starter | 49 | 2022-11-03 | 2023-02-03 |
| 2 | 1 | growth | 149 | 2023-02-03 | NULL |
| 3 | 2 | growth | 149 | 2022-12-15 | 2023-06-15 |
| 4 | 3 | starter | 49 | 2023-01-31 | NULL |
| 5 | 4 | growth | 149 | 2023-02-06 | NULL |
| 6 | 5 | enterprise | 499 | 2023-02-22 | NULL |
| 7 | 6 | starter | 49 | 2023-03-01 | 2023-04-01 |
| 8 | 7 | growth | 149 | 2023-03-22 | NULL |
| 9 | 8 | enterprise | 499 | 2023-04-28 | NULL |
| 10 | 2 | starter | 49 | 2023-06-15 | NULL |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, plan and num_subs, with one row per tier that appears in the table, counting every subscription regardless of whether it has since been cancelled. Rows come back in alphabetical order of plan.
Example output — shape only; the figures below are invented, not this data's answer.
| plan | num_subs |
|---|---|
| enterprise | 5 |
| growth | 9 |
| starter | 11 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in