Postgres Practice 11/12

LAG: comparing to the previous row

"How did each rep's sales change from month 1 to month 2?" needs something SQL rows famously don't have: a way to look at the previous row. Each row knows its own month's amount; the change requires this month minus last month - two different rows in one calculation.

LAG() is the window function built for exactly this. It reaches back one row within the window and hands you a value from there:

LAG(amount) OVER (PARTITION BY rep ORDER BY month)

PARTITION BY rep keeps each rep's timeline separate - ana's month 1 must never be treated as the "previous month" of ben's. ORDER BY month defines what "previous" means. Subtract the lagged value from the current one and you have the month-over-month change.

One behavior to expect rather than fear: the first row of each partition has no previous row, so LAG returns NULL there - and anything minus NULL is NULL. That's correct output, not a bug: month 1 genuinely has no change to report. (LEAD() is the mirror image - it looks forward one row.)

Same sales table: rep, region, month, amount.

Your task: for every sale, show rep, month, amount, and the change from that rep's previous month as a column named change (current amount minus the LAG of amount, partitioned by rep, ordered by month). Order the results by rep, then month.

You'll practice:

Reaching into the previous row with LAG() OVER (...)
Partitioning so each rep's timeline stays independent

-- Show each rep's month-over-month change.
-- The change needs this row's amount MINUS the previous row's amount.
SELECT rep, month, amount
FROM sales
ORDER BY rep, month;