Upgraded, regretted, downgraded
Sales at Cloudpeak celebrates every upgrade, but the pricing team suspects some customers upgrade, feel the pain of the higher bill and fall back within weeks. They want to see these reversals so they can interview the customers.
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 every upgrade (a subscription row whose previous row of the same account had a lower mrr) that is followed directly by a downgrade (the next row of the same account has a lower mrr) starting no more than 90 days after the upgrade started. Return company, upgraded_on (start_date of the upgrade row), from_plan, upgraded_to, downgraded_on, downgraded_to and days_between (whole days from upgrade to downgrade). Sort by company, then upgraded_on.
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?
Join the subscriptions table to itself three times: previous row, upgrade row, next row. The link between rows is an end_date equal to the next start_date.
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.