LAG and LEAD: the previous row, and the change since it

Reading the row before this one

Month-over-month growth, days since the last order, whether a price went up or down: every one of these needs a row to see its predecessor. lag() does exactly that.

SELECT month, revenue,
       lag(revenue) OVER (ORDER BY month)              AS prev_revenue,
       revenue - lag(revenue) OVER (ORDER BY month)    AS change,
       (revenue - lag(revenue) OVER (ORDER BY month))
         / nullif(lag(revenue) OVER (ORDER BY month), 0) AS pct_change
FROM monthly_revenue;

lag(revenue) returns the value of revenue from the row one position earlier in the window’s order. The first row has no predecessor and gets NULL, which is why the percentage change is guarded with nullif. lag(revenue, 12) reaches twelve rows back, which is the same month last year if, and only if, every month is present. lag(revenue, 1, 0) supplies a default instead of NULL. lead() is the mirror image and looks forward.

Per-entity, with a partition

Add a partition and each entity gets its own sequence:

SELECT customer_id, placed_at,
       placed_at - lag(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at, order_id)
         AS days_since_last_order
FROM orders;

A customer’s first order gets NULL, every later one gets the gap. avg(days_since_last_order) over that result is the average reorder interval, and lead() in the same shape gives “days until the next order”, which is what you compute when the question is whether a customer has gone quiet compared with their own habit.

The gap is in rows, not in time

lag() looks back one row. If a customer has months with no orders, the previous row is not the previous month; it is whenever they last ordered. For “revenue in the previous calendar month” on a table with gaps, either join to a spine first so that every month exists as a row, or look the value up by date with a self-join on month - interval '1 month'. The window over a spine is cleaner and is the usual answer. The mistake to avoid is computing month-over-month change with lag() on a table that skips a month, which reports a two-month change as if it were one.

first_value, last_value, and a trap

first_value(price) OVER (PARTITION BY product_id ORDER BY changed_at) gives every row the product’s original price. last_value() should, by symmetry, give the latest, and it does not:

last_value(price) OVER (PARTITION BY product_id ORDER BY changed_at)
-- returns each row's OWN price

The reason is the default frame from the previous lesson. It runs from the start of the partition to the current row, so the “last” value in the frame is always the current one. To see the genuine last value the frame has to extend to the end:

last_value(price) OVER (PARTITION BY product_id ORDER BY changed_at
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

This is the most searched-for window function bug there is, and it is the frame every time. nth_value(price, 2) has the same behaviour and the same fix.

Direction and streaks

Comparing a row with its predecessor is how you classify movement: CASE WHEN price > lag(price) OVER w THEN 'up' WHEN price < lag(price) OVER w THEN 'down' ELSE 'flat' END. Count the ‘up’ rows and you have how often the price rose. Flag the rows where the direction changed and take a running sum of the flags, and you have numbered the runs, which is the next lesson’s method for streaks and sessions.

What lag cannot do

It looks back a fixed number of rows in one ordering. “The previous order from the same branch” when the window is partitioned by customer needs a different partition and therefore a second window, and “the previous order over ₹1,000” needs a filter before the window, because lag() cannot skip rows conditionally. A lag() on a filtered CTE handles the second case; the first is two windows in one query, which the first lesson of this module showed is fine.

The habit

Every lag() answers two questions you should be able to state: ordered by what, and partitioned by what. And any last_value() without an explicit frame is a bug until proved otherwise.