Paying customers who never opened the product
A customer who pays but never uses the product is a churn waiting to happen. Cloudpeak's customer success team wants to call these "silent" accounts before their next renewal. Product usage is recorded in events.
Dataset: Cloudpeak. Every plan change writes a new subscriptions row: the old row gets an end_date and the new row starts on that same date. A row with end_date NULL is the current plan. A customer who cancels has a last row whose end_date has no follow-up row.
Task: find accounts that have a current subscription (end_date IS NULL) and have no row at all in events. Return company, country, plan and mrr, sorted by mrr from highest to lowest, then by company.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | company TEXT | country TEXT | industry TEXT | signup_date TEXT |
|---|---|---|---|---|
| 1 | Aurora Freight | Germany | Logistics | 2025-09-22 |
| 2 | Bluegrid Energy | United Kingdom | Energy | 2025-10-13 |
| 3 | Cedar Health Labs | United States | Healthcare | 2026-03-07 |
| 4 | Delta Pay Systems | Nigeria | Fintech | 2025-12-26 |
| 5 | Evergreen Retail Group | Canada | Retail | 2025-02-12 |
| 6 | Falcon Aerospace | United Arab Emirates | Aerospace | 2025-02-12 |
Showing the first 6 of 40 rows.
| id INTEGER | account_id INTEGER | user_id INTEGER | feature TEXT | event_ts TEXT |
|---|---|---|---|---|
| 1 | 1 | 12 | api | 2025-09-24 16:05:34 |
| 2 | 1 | 13 | api | 2026-04-29 12:15:40 |
| 3 | 2 | 21 | api | 2025-10-15 15:14:48 |
| 4 | 2 | 24 | sso | 2025-10-14 13:06:32 |
| 5 | 2 | 24 | alerts | 2025-10-16 10:57:11 |
| 6 | 3 | 33 | alerts | 2026-06-01 10:37:06 |
Showing the first 6 of 149 rows.
| id INTEGER | account_id INTEGER | issued_on TEXT | due_on TEXT | paid_on TEXT | amount_usd REAL |
|---|---|---|---|---|---|
| 1 | 1 | 2026-01-01 | 2026-01-31 | 2026-01-22 | 499 |
| 2 | 1 | 2026-02-01 | 2026-03-03 | 2026-02-13 | 499 |
| 3 | 1 | 2026-03-01 | 2026-03-31 | 2026-04-23 | 499 |
| 4 | 1 | 2026-04-01 | 2026-05-01 | 2026-04-23 | 499 |
| 5 | 1 | 2026-05-01 | 2026-05-31 | 2026-05-20 | 499 |
| 6 | 1 | 2026-06-01 | 2026-07-01 | NULL | 499 |
Showing the first 6 of 168 rows.
| id INTEGER | account_id INTEGER | plan TEXT | mrr REAL | start_date TEXT | end_date TEXT |
|---|---|---|---|---|---|
| 1 | 1 | Scale | 499 | 2025-09-22 | NULL |
| 2 | 2 | Starter | 49 | 2025-10-13 | NULL |
| 3 | 3 | Growth | 199 | 2026-03-07 | NULL |
| 4 | 4 | Growth | 199 | 2025-12-26 | NULL |
| 5 | 5 | Starter | 49 | 2025-02-12 | 2026-02-01 |
| 6 | 5 | Growth | 199 | 2026-02-01 | 2026-05-01 |
Showing the first 6 of 65 rows.
| id INTEGER | account_id INTEGER | opened_ts TEXT | resolved_ts TEXT | priority TEXT |
|---|---|---|---|---|
| 1 | 2 | 2025-11-28 08:44:00 | NULL | Normal |
| 2 | 37 | 2026-01-29 15:51:00 | 2026-01-31 09:55:00 | Low |
| 3 | 18 | 2025-05-01 13:33:00 | 2025-05-04 09:13:00 | Low |
| 4 | 11 | 2025-09-24 16:09:00 | 2025-09-24 19:40:00 | Normal |
| 5 | 39 | 2025-12-06 15:34:00 | 2025-12-07 14:17:00 | High |
| 6 | 15 | 2026-03-20 11:06:00 | 2026-03-23 20:44:00 | Low |
Showing the first 6 of 40 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
A LEFT JOIN to events leaves NULLs for accounts without any event.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.