Classify Every Plan Change as Upgrade or Downgrade
PlanHistory records the plan an account moved to each time it changed plans; an account's earliest row is its sign-up, not a change.
- Difficulty: Medium
- Topics: Window Functions, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #485
Problem statement
PlanHistory records the plan an account moved to each time it changed plans; an account's earliest row is its sign-up, not a change. Finance wants every real change labelled by price: upgrade when the new plan's monthly price is higher than the previous plan's, downgrade when lower, and lateral when the prices are equal.
Return one row per change (sign-ups excluded) with the columns account_id, changed_on, from_plan, to_plan and direction. Order by account_id, then changed_on.
Tables
Table: Plan
| Column | Type |
|---|---|
| plan_name | varchar |
| monthly_price | int |
Primary key: plan_name.
Monthly price in rupees.
Table: PlanHistory
| Column | Type |
|---|---|
| account_id | int |
| changed_on | date |
| plan_name | varchar |
Primary key: account_id, changed_on.
One row per plan an account switched to, on the day it took effect; an account changes plan at most once a day.
Examples
Example 1
Plan
| plan_name | monthly_price |
|---|---|
| Mobile | 149 |
| Basic | 199 |
| Super | 199 |
| Standard | 499 |
| Premium | 649 |
PlanHistory
| account_id | changed_on | plan_name |
|---|---|---|
| 7001 | 2024-01-04 | Mobile |
| 7001 | 2024-03-10 | Standard |
| 7001 | 2024-06-01 | Basic |
| 7002 | 2024-02-15 | Premium |
| 7003 | 2024-01-20 | Basic |
| 7003 | 2024-02-20 | Super |
| 7003 | 2024-05-02 | Premium |
Output
| account_id | changed_on | from_plan | to_plan | direction |
|---|---|---|---|---|
| 7001 | 2024-03-10 | Mobile | Standard | upgrade |
| 7001 | 2024-06-01 | Standard | Basic | downgrade |
| 7003 | 2024-02-20 | Basic | Super | lateral |
| 7003 | 2024-05-02 | Super | Premium | upgrade |
How to solve Classify Every Plan Change as Upgrade or Downgrade
A change is a pair of consecutive rows of one account, so the natural tool is LAG(plan_name) OVER (PARTITION BY account_id ORDER BY changed_on): on each row it returns the plan the account was on just before. The sign-up row gets NULL because nothing precedes it.
Then join Plan twice — once for the old plan, once for the new — and compare the prices in a CASE. Because the old plan is joined with an inner join, the sign-up row (with from_plan NULL) matches no plan and drops out, which is exactly the "sign-ups excluded" rule. Two plans can cost the same (Basic and Super are both ₹199), and moving between them is lateral; moving to the same plan again would be lateral too.
Without windows, the previous row is the one with the greatest changed_on before the current one, found by a correlated MAX and joined back. That is quadratic per account; the window plan is one sort by (account, date).
Reference solution (MySQL)
WITH h AS (
SELECT account_id, changed_on, plan_name AS to_plan,
LAG(plan_name) OVER (PARTITION BY account_id ORDER BY changed_on) AS from_plan
FROM PlanHistory
)
SELECT h.account_id, h.changed_on, h.from_plan, h.to_plan,
CASE WHEN n.monthly_price > o.monthly_price THEN 'upgrade'
WHEN n.monthly_price < o.monthly_price THEN 'downgrade'
ELSE 'lateral' END AS direction
FROM h
JOIN Plan o ON o.plan_name = h.from_plan
JOIN Plan n ON n.plan_name = h.to_plan
ORDER BY h.account_id, h.changed_onAnother way
SELECT c.account_id, c.changed_on, p.plan_name AS from_plan, c.plan_name AS to_plan,
IF(nc.monthly_price > np.monthly_price, 'upgrade', IF(nc.monthly_price < np.monthly_price, 'downgrade', 'lateral')) AS direction
FROM PlanHistory c
JOIN PlanHistory p ON p.account_id = c.account_id
AND p.changed_on = (SELECT MAX(x.changed_on) FROM PlanHistory x WHERE x.account_id = c.account_id AND x.changed_on < c.changed_on)
JOIN Plan nc ON nc.plan_name = c.plan_name
JOIN Plan np ON np.plan_name = p.plan_name
ORDER BY c.account_id, c.changed_on← Artist Royalties for May 2024 · Listeners With a Song on Repeat →