Upgrades, Downgrades and Lateral Plan Moves

Medium
414.

Upgrades, Downgrades and Lateral Plan Moves

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

ColumnType
change_idint
account_idint
changed_ondate
planvarchar
monthly_priceint

Primary key: change_id.

One row per plan an account moved onto, with the plan's monthly price in rupees at the time.

Example

The rows must come back in the order shown.

plan_changes

change_idaccount_idchanged_onplanmonthly_price
1102024-01-05starter2999
2102024-03-01growth9999
3102024-06-15team9999
4112024-02-10business24999
5112024-05-20growth9999
6122024-04-01starter2999
7102024-09-01enterprise85000

Output

change_idaccount_idfrom_planto_plandirection
210startergrowthupgrade
310growthteamlateral
710teamenterpriseupgrade
511businessgrowthdowngrade
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.

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

ColumnType
change_idint
account_idint
changed_ondate
planvarchar
monthly_priceint

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_idaccount_idchanged_onplanmonthly_price
1102024-01-05starter2999
2102024-03-01growth9999
3102024-06-15team9999
4112024-02-10business24999
5112024-05-20growth9999
6122024-04-01starter2999
7102024-09-01enterprise85000

Output

change_idaccount_idfrom_planto_plandirection
210startergrowthupgrade
310growthteamlateral
710teamenterpriseupgrade
511businessgrowthdowngrade

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_on

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