32. Analytic functions: LAG, LEAD, FIRST_VALUE

Reaching to a different row, without a self-join

Every function in this lesson answers some version of "what was the value at a different row, relative to this one" — the kind of question that, before window functions, required a self-join (Module 4) with a fragile row_number join condition. These functions do it directly.

LAG and LEAD: look backward or forward by N rows

SELECT film_id, title, rental_rate,
    lag(rental_rate) OVER (ORDER BY film_id) AS prev_rate,
    lead(rental_rate) OVER (ORDER BY film_id) AS next_rate
FROM film
ORDER BY film_id
LIMIT 10;

LAG(column) returns that column's value from the previous row (one row back by default); LEAD(column) returns it from the next row. Both accept an optional second argument for how many rows to look back/forward (LAG(column, 3)), and an optional third argument giving a default value to use instead of NULL when there's no such row (e.g. the very first row has no "previous" row — LAG returns NULL there unless you supply a default):

SELECT film_id, title, rental_rate,
    lag(rental_rate, 1, 0) OVER (ORDER BY film_id) AS prev_rate_defaulted
FROM film
ORDER BY film_id
LIMIT 3;

The single most common real use of LAG: computing the difference between consecutive rows — a day-over-day change, a running delta:

SELECT film_id, title, rental_rate,
    rental_rate - lag(rental_rate) OVER (ORDER BY film_id) AS change_from_prev
FROM film
ORDER BY film_id
LIMIT 10;

FIRST_VALUE and LAST_VALUE: the edges of the frame

SELECT film_id, title, rating, rental_rate,
    first_value(title) OVER (PARTITION BY rating ORDER BY rental_rate DESC) AS most_expensive_in_rating
FROM film
ORDER BY rating, rental_rate DESC
LIMIT 10;

FIRST_VALUE returns the value from the first row of the current frame — combined with PARTITION BY/ORDER BY, this answers "what's the top row's value, repeated alongside every row in its group," without a separate join back to a MAX-filtered subquery.

LAST_VALUE has a well-known trap, directly caused by the default frame from the previous lesson — recall that the actual default frame (when ORDER BY is present) is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE's CURRENT ROW means "through the end of the current row's entire peer group of ties," not "stop at this exact physical row." Since rental_rate has heavy ties (only 3 distinct values across all 1000 films), every row tied at the same rate shares the same peer group — so LAST_VALUE with the default frame returns the same value for every row in that tie group (the last row within the tie, in whatever order ties are physically stored), not the true last row of the whole partition, and not the current row's own value either:

-- Misleading result: every row tied at the same rental_rate gets the
-- SAME last_value — the last row within that tie group, not the true
-- last row of the whole rating partition (the actual cheapest film).
SELECT film_id, title, rating, rental_rate,
    last_value(title) OVER (PARTITION BY rating ORDER BY rental_rate DESC) AS misleading_last
FROM film
ORDER BY rating, rental_rate DESC
LIMIT 10;

-- CORRECT: explicitly extend the frame to the end of the partition.
SELECT film_id, title, rating, rental_rate,
    last_value(title) OVER (
        PARTITION BY rating ORDER BY rental_rate DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS cheapest_in_rating
FROM film
ORDER BY rating, rental_rate DESC
LIMIT 10;

This is a genuinely common mistake, and it's a direct consequence of the previous lesson's frame-default gotcha — reason enough that this course introduces LAST_VALUE after frames rather than before, so the trap is already familiar rather than a surprise. (If the ordering column instead had no ties at all, the default RANGE frame's peer groups would each be a single row, and LAST_VALUE would echo the current row's own value — also wrong, just wrong in a different, more obviously-suspicious way.)

NTH_VALUE: the Nth row of the frame

-- The SECOND most expensive film's title, within each rating.
SELECT film_id, title, rating, rental_rate,
    nth_value(title, 2) OVER (
        PARTITION BY rating ORDER BY rental_rate DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_most_expensive
FROM film
ORDER BY rating, rental_rate DESC
LIMIT 10;

Same frame-boundary sensitivity as LAST_VALUE — NTH_VALUE only sees rows within the current frame, so an under-extended frame can make it return NULL (if the Nth row falls outside a too-narrow frame) rather than a wrong-but-plausible value, which is at least easier to notice as a bug than LAST_VALUE's silent misbehavior.

Check yourself

1. What does LAG(rental_rate, 2, 0) mean, in plain language?
2. Why does LAST_VALUE() with the default frame often fail to return the actual last row's value of the whole partition — what does it return instead, and why?
3. What's the fix for the LAST_VALUE trap?