Upgrades, Downgrades and Lateral Plan Moves — SQL Problem
Billing logs a row every time an account moves onto a plan; an account's first row is its initial purchase and is not a change.
- Difficulty: Medium
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #414
Problem statement
Billing logs a row every time an account moves onto a plan; an account's first row is its initial purchase and is not a change. Every later row is compared with the same account's previous row: it is an upgrade when its monthly_price is higher, a downgrade when lower, and lateral when equal (for example switching between two plans at the same price).
Return change_id, account_id, from_plan, to_plan and direction for every change except initial purchases, ordered by account_id, then changed_on. No account has two rows on the same day.
Tables
Table: plan_changes
| Column | Type |
|---|---|
| change_id | int |
| account_id | int |
| changed_on | date |
| plan | varchar |
| monthly_price | int |
Primary key: change_id.
One row per plan an account moved onto, with the plan's monthly price in rupees at the time.
Examples
Example 1
plan_changes
| change_id | account_id | changed_on | plan | monthly_price |
|---|---|---|---|---|
| 1 | 10 | 2024-01-05 | starter | 2999 |
| 2 | 10 | 2024-03-01 | growth | 9999 |
| 3 | 10 | 2024-06-15 | team | 9999 |
| 4 | 11 | 2024-02-10 | business | 24999 |
| 5 | 11 | 2024-05-20 | growth | 9999 |
| 6 | 12 | 2024-04-01 | starter | 2999 |
| 7 | 10 | 2024-09-01 | enterprise | 85000 |
Output
| change_id | account_id | from_plan | to_plan | direction |
|---|---|---|---|---|
| 2 | 10 | starter | growth | upgrade |
| 3 | 10 | growth | team | lateral |
| 7 | 10 | team | enterprise | upgrade |
| 5 | 11 | business | growth | downgrade |
How to solve Upgrades, Downgrades and Lateral Plan Moves
Every classification compares a row with the previous row of the same account, which is what LAG(…) OVER (PARTITION BY account_id ORDER BY changed_on) returns. Lag both the plan (for from_plan) and the price (for the comparison) in a derived table, then classify with a CASE outside it.
An account's first row has nothing before it, so its lagged price is NULL. Filtering prev_price IS NOT NULL removes the initial purchases — and must happen outside the window query, because WHERE runs before window functions: filtering inside would change which row is "previous". Prices never NULL, so the filter only ever removes first rows.
The comparison is on price because plan names do not order themselves: moving from growth to team at the same ₹9,999 is lateral. The self-join alternative finds the previous row with a correlated MAX(changed_on) < current, which relies on dates being unique per account, as the statement promises; the window version needs no such promise and is one sort.
Reference solution (MySQL)
SELECT change_id, account_id, from_plan, to_plan,
CASE WHEN monthly_price > prev_price THEN 'upgrade'
WHEN monthly_price < prev_price THEN 'downgrade'
ELSE 'lateral' END AS direction
FROM (
SELECT change_id, account_id, changed_on, plan AS to_plan, monthly_price,
LAG(plan) OVER (PARTITION BY account_id ORDER BY changed_on) AS from_plan,
LAG(monthly_price) OVER (PARTITION BY account_id ORDER BY changed_on) AS prev_price
FROM plan_changes
) c
WHERE prev_price IS NOT NULL
ORDER BY account_id, changed_onAnother way
SELECT cur.change_id, cur.account_id, prev.plan AS from_plan, cur.plan AS to_plan,
IF(cur.monthly_price > prev.monthly_price, 'upgrade', IF(cur.monthly_price < prev.monthly_price, 'downgrade', 'lateral')) AS direction
FROM plan_changes cur
JOIN plan_changes prev ON prev.account_id = cur.account_id
AND prev.changed_on = (SELECT MAX(p.changed_on) FROM plan_changes p WHERE p.account_id = cur.account_id AND p.changed_on < cur.changed_on)
ORDER BY cur.account_id, cur.changed_on← Monthly Active Users, New Versus Returning · Seat-Based Monthly Bill for Every Workspace →