Classify Every Plan Change as Upgrade or Downgrade

Medium
485.

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. 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

ColumnType
plan_namevarchar
monthly_priceint

Primary key: plan_name.

Monthly price in rupees.

Table: PlanHistory

ColumnType
account_idint
changed_ondate
plan_namevarchar

Primary key (together): 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.

Example

The rows must come back in the order shown.

Plan

plan_namemonthly_price
Mobile149
Basic199
Super199
Standard499
Premium649

PlanHistory

account_idchanged_onplan_name
70012024-01-04Mobile
70012024-03-10Standard
70012024-06-01Basic
70022024-02-15Premium
70032024-01-20Basic
70032024-02-20Super
70032024-05-02Premium

Output

account_idchanged_onfrom_planto_plandirection
70012024-03-10MobileStandardupgrade
70012024-06-01StandardBasicdowngrade
70032024-02-20BasicSuperlateral
70032024-05-02SuperPremiumupgrade
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
plan_namevarchar
monthly_priceint

Primary key: plan_name.

Monthly price in rupees.

Table: PlanHistory

ColumnType
account_idint
changed_ondate
plan_namevarchar

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_namemonthly_price
Mobile149
Basic199
Super199
Standard499
Premium649

PlanHistory

account_idchanged_onplan_name
70012024-01-04Mobile
70012024-03-10Standard
70012024-06-01Basic
70022024-02-15Premium
70032024-01-20Basic
70032024-02-20Super
70032024-05-02Premium

Output

account_idchanged_onfrom_planto_plandirection
70012024-03-10MobileStandardupgrade
70012024-06-01StandardBasicdowngrade
70032024-02-20BasicSuperlateral
70032024-05-02SuperPremiumupgrade

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_on

Another 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 →