Which Accounts Have We Lost?
Customer success at Loopwire runs a win-back list every quarter: companies that were paying customers and are not any more. Somebody who downgraded is not on it — they are still a customer and belong to a different conversation. The list is for accounts that have gone completely quiet.
The difficulty is that "has nothing running" is not a fact the data states. The subscriptions table records subscriptions, and each one either has an end date or does not; a fully churned account is one for which every row has an end date. That is a statement about all of an account's rows at once, and looking at any single row cannot establish it — account 1 has a closed starter plan and is still very much a customer.
accounts — one row per customer company.
| id | company_name | industry | signup_date |
|---|---|---|---|
| 1 | Northwind Traders | retail | 2022-11-03 |
| 2 | Vertex Analytics | software | 2022-12-15 |
| 3 | Bluepeak Logistics | logistics | 2023-01-20 |
| 4 | Fernwood Studio | media | 2023-02-05 |
| 5 | Cobalt Health | healthcare | 2023-02-18 |
| 6 | Ridgeline Capital | finance | 2023-03-01 |
| 7 | Sable & Co | retail | 2023-03-22 |
| 8 | Hearth Robotics | manufacturing | 2023-04-10 |
subscriptions — one row per subscription, past or present. end_date is the day it stopped, and is NULL for one that 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single column, company_name, holding every account with no still-running subscription at all. An account with even one subscription that has no end_date does not qualify, however many closed ones sit beside it.
Example output — shape only, on an invented account.
| company_name |
|---|
| Quillstone Media |
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