The Churn Rate
The board pack has a slot for one retention figure, and Loopwire's finance lead wants the one everybody means by churn: of the customers the company has ever signed, what share are no longer paying anything at all, as a percentage.
The catch is that this table is not a list of customers. Loopwire never edits a contract when somebody moves tier — it stops the old contract and writes a fresh one the same day — so one account can own several rows, and a stopped contract on its own proves nothing about whether that customer left. Eight accounts own the ten contracts here, and the percentage the finance lead wants is out of the eight.
subscriptions — one row per contract. account_id identifies the customer, and the same customer may own several of these rows. plan is the tier that contract bought. mrr is what it 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, churn_rate_pct, holding the percentage of the accounts in this table that have no contract still running, rounded to 1 decimal place. Accounts that are still paying stay in the denominator.
Example output
| churn_rate_pct |
|---|
| 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