Who's Changed Plans at Least Once?
Loopwire's product team is trying to understand upgrade behaviour, and before any of that they need to know who has ever moved at all. An account that signed up once and has sat on the same plan since tells them nothing; an account with a second subscription record has been through something — an upgrade, a downgrade, or a lapse and a return — and those are the accounts worth interviewing.
Every one of those movements leaves the same trace: the old subscription is closed off and a new row is written. So the accounts of interest are exactly the ones carrying more than one row in the subscriptions table, whatever the reason.
subscriptions — one row per subscription, past or present. A new row is written every time an account starts a subscription, so an account that has never changed anything has exactly one.
| 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 a single column, account_id, holding every account with more than one subscription row, whether or not those subscriptions have ended. An account with exactly one row does not appear. Hand the ids back smallest first.
Example output — shape only, on invented ids.
| account_id |
|---|
| 4 |
| 7 |
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