The Upgrade Story

In Loopwire's database a customer changing tier is never an edit: the old contract is closed and a fresh row is written. So the change itself has no column of its own — it exists only as a relationship between two rows on the same account, one stopped and one still running. Product is writing the expansion story for the investor update and wants those customers listed: the ones who came back on something bigger.

Two things make that awkward. The tier names give no clue about size on their own — nothing in the word "growth" says whether it costs more or less than "starter", and the only place that ordering is recorded is the money each contract bills. And not every customer who changed tier moved up; some of them cut their spend, and those do not belong in an expansion story.

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.

The table already exists in the database — there is nothing to create or load.

Task: Write a query that returns three columns, account_id, old_plan and new_plan, one row for each account that closed a contract and now runs one billing strictly more per month. old_plan is the tier of the stopped contract, new_plan the tier of the running one. Rows come back in ascending account_id order.

Example output