The Churn Post-Mortem
Churn is what Loopwire calls a customer stopping their subscription, and every churn gets a post-mortem call. The retention lead is booking those calls and wants the worklist most-recent-departure first, because a customer who left last month remembers why and a customer who left a year ago does not. A contract number is no use on a calendar invite — the list has to carry the company's actual name.
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 | — |
accounts — one row per customer company. industry is the sector that company works in; signup_date is the day it created its Loopwire account.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns three columns, company_name, plan and end_date, one row for every contract that has stopped. Most recent ending first.
Example output
Shape only — these companies and dates are invented; the tier names are the real ones:
| company_name | plan | end_date |
|---|---|---|
| Orbit Grocers | growth | 2024-02-09 |
| Larkspur Freight | starter | 2023-12-01 |
| Maple Row Dental | enterprise | 2023-09-14 |
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