How Long Do Customers Typically Stay Before Churning?
Loopwire's customer success team is deciding when to start working an at-risk account, and the question behind that decision is how much runway they usually have. Their proxy: among the contracts that have already ended, how long did they typically last?
Only ended contracts can answer it, because only they have a second date. A contract still running has an empty end_date and no length yet — it has lasted a while and will last longer, and there is no honest number to average it into. Seven of the ten contracts here are still running.
The dates are stored as text as well, so the gap between two of them is not a subtraction you can write directly.
subscriptions — one row per subscription contract. start_date is the day it began and end_date the day it ended, both as text in YYYY-MM-DD form. end_date is NULL while the contract 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 two columns. avg_lifetime_days is the mean number of days from start_date to end_date, taken across the contracts that have ended, rounded to 2 decimal places. ended_contracts is how many contracts that average was taken over. Contracts still running are left out of both figures entirely rather than counted as zero-length or as running-to-today. Each day count is the plain difference between the two dates, with no rounding before the average is taken.
Example output
| avg_lifetime_days | ended_contracts |
|---|---|
| 214.5 | 6 |
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