Who's Still Paying?
Loopwire sells software on monthly contracts. Finance is closing the month, and the revenue report opens with a headcount of live contracts — not everything ever sold, only what is still being billed. Cancelled contracts are never deleted from the database, because their history is the raw material for every churn study Loopwire runs. That is convenient later and inconvenient now: the table is longer than the answer.
subscriptions — one row per contract. account_id points at a row in accounts. plan is the tier the customer bought. mrr is what that contract bills every month, in dollars. start_date is the day it began. A contract that has stopped carries the day it stopped in end_date; one that is still running has nothing there, shown as — below and stored as NULL.
| 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 | — |
| 3 | 2 | growth | 149 | 2022-12-15 | 2023-06-15 |
| 4 | 3 | starter | 49 | 2023-01-31 | — |
| 5 | 4 | growth | 149 | 2023-02-06 | — |
| 6 | 5 | enterprise | 499 | 2023-02-22 | — |
| 7 | 6 | starter | 49 | 2023-03-01 | 2023-04-01 |
| 8 | 7 | growth | 149 | 2023-03-22 | — |
| 9 | 8 | enterprise | 499 | 2023-04-28 | — |
| 10 | 2 | starter | 49 | 2023-06-15 | — |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row and one column, active_subs, holding how many contracts are still running. A contract that has stopped carries the day it stopped; a contract still running has nothing recorded there.
Example output
If twenty-six contracts were still running, the single row would read:
| active_subs |
|---|
| 26 |
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