Interview Question Bank · LAG · PARTITION BY · CASE
5. Upgrade, downgrade or lateral
MediumSaaSQuestion #113
Concept: LAG · PARTITION BY · CASEshow
LAG(col) OVER (PARTITION BY entity ORDER BY time) reads the previous row's value for the same entity, so each row can be compared with what came before. Without PARTITION BY, the previous row may belong to someone else. Each entity's first row has no previous value (NULL), so filter it out when you only want changes.
Example (different data):
SELECT employee_id, effective_on, title,
CASE WHEN salary > LAG(salary) OVER w THEN 'raise'
WHEN salary < LAG(salary) OVER w THEN 'cut'
ELSE 'same' END AS move
FROM job_history
WINDOW w AS (PARTITION BY employee_id ORDER BY effective_on);Your task
Tallyboard's revenue team wants every plan change classified by what happened to the price, not by the plan's name: 'Team Annual' is billed yearly but costs the same per month as 'Team'.
For every subscription that replaced an earlier one on the same account, return account_id, changed_on (its start_date), from_plan, to_plan and change_type: 'upgrade' if mrr went up, 'downgrade' if it went down, 'lateral' if it stayed the same. Sort by changed_on, then account_id.
Starting Postgres in your browser…
Checking your access…