How Many Customers Are Still Paying?
Loopwire is a small business-to-business software company, and its CEO wants one number before walking into a board meeting: how many subscriptions are still being paid for today.
The subscription table keeps history rather than only the present. A cancelled subscription is not deleted; it stays as a row with the day it ended written into end_date. A subscription that is still running has nothing in that slot — it holds NULL, SQL's marker for a value that was never recorded, which is not the same as an empty string or a zero. Note also that one company can appear on several rows, because a customer that upgraded has both the old row and the new one.
subscriptions — one row per subscription ever sold. 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 a single row with a single column, active_subs, holding the number of subscription rows that have no end date recorded.
Example output — shape only. If twelve subscriptions had no end date on them, the single row would read:
| active_subs |
|---|
| 12 |
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