What's Our Overall Churn Rate?
Loopwire's board wants one retention figure on the quarterly slide: of all the customers the company has ever signed, what share of them have left altogether. A customer who left is one Loopwire no longer bills at all — not one who changed what they pay.
That difference is the whole of the difficulty, because the table does not hold customers, it holds contracts. When a customer changes tier, Loopwire closes the old contract and writes a new one the same day, so one account can own several contract rows and a stopped contract is not by itself evidence that anybody left. Ten contracts belong to eight accounts, and three of the ten have stopped — but the figure the board wants is out of eight, not out of ten.
A contract that is still running has no end date recorded: the end_date column is empty for it, and stays empty until the day it ends. So "still a customer" is not a status column you can read anywhere; it is the presence or absence of a date, judged across every contract an account owns.
subscriptions — one row per subscription contract. account_id identifies the customer, and the same customer may appear on several rows. mrr is what the contract bills each month. end_date is the day the contract ended, 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 exactly one row with a single column, churn_rate: the number of accounts that have no contract still running, divided by the number of accounts that appear in the table at all — a fraction between 0 and 1, not a percentage. Every account in the table belongs in the denominator, including the ones still paying. The answer is not rounded.
Example output — shape only. If 3 of 12 accounts had nothing still running, the single row would read:
| churn_rate |
|---|
| 0.25 |
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