Questions: LAG, LEAD, and OFFSET: Accessing Rows in Windows

Question 1

You want to compute month-over-month revenue change for each row in a sales table ordered by month. Which approach is correct?

• A. SELECT month, revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change FROM sales
• B. SELECT month, revenue - LAG(revenue, 1) FROM sales ORDER BY month
• C. SELECT s1.month, s1.revenue - s2.revenue AS mom_change FROM sales s1 JOIN sales s2 ON s1.month = s2.month + 1
• D. SELECT month, revenue - FIRST_VALUE(revenue) OVER (ORDER BY month) AS mom_change FROM sales

Answer: (A)

LAG(revenue, 1) OVER (ORDER BY month) returns the revenue from the preceding row in the ordered window, so subtracting it gives the month-over-month change in a single, readable expression. Option B omits the OVER clause, which makes LAG syntactically invalid — LAG is a window function and requires OVER. Option C is the old self-join pattern that LAG replaces; it is more verbose and harder to read. Option D subtracts the very first row's revenue, giving change from the start rather than from the prior month.

Question 2

A query uses LAST_VALUE(price) OVER (PARTITION BY product ORDER BY sale_date). What does this return on each row with the default window frame?

• A. The price from the very last sale of that product
• B. The price from the very first sale of that product
• C. The price from the current row itself
• D. NULL, because LAST_VALUE requires an explicit frame

Answer: (C)

This is the most common LAST_VALUE gotcha. The default window frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — it only includes rows from the start of the partition up to the current row. So LAST_VALUE of that frame is always the current row's own value. To get the actual last row of the partition, you must explicitly extend the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This is why FIRST_VALUE works intuitively with the default frame (the first row in the partition is always in the frame) but LAST_VALUE does not.

Question 3

When a partition boundary is crossed, LAG returns NULL (or the specified default) rather than looking into the previous partition.

• T. True
• F. False

Answer: True

PARTITION BY resets the window for each group. LAG navigating backward stops at the partition boundary — there is no 'previous row' for the first row of each partition, so LAG returns NULL (or the default value if you specify one as the third argument). This is by design: comparing January's revenue to December's makes sense within a single product or region, but mixing data across partitions would produce nonsensical results. The partition acts as a logical fence that confines row navigation.

Question 4

LEAD and LAG are equivalent to window-aggregate functions like SUM OVER — they compute a value derived from most rows in the window frame.

• T. True
• F. False

Answer: False

LAG and LEAD are offset functions, not aggregate functions. They access a specific row at a fixed offset from the current row (the previous row, the next row, or the nth row back/forward), returning that row's column value directly. Aggregate window functions like SUM, AVG, and COUNT compute a summary statistic across all rows in the window frame. The distinction matters: LAG returns a raw value from a specific row, while SUM returns a computed value derived from many rows. Offset functions do not respect window frame specifications the same way aggregates do.

Question 5

Why did SQL need LAG and LEAD at all? What was the alternative before window functions, and what problem does the new approach solve?

Model answer: Before LAG and LEAD, comparing a row to its neighbor required a self-join — joining the table to itself on an offset key (e.g., ON s1.month = s2.month - 1). LAG and LEAD replace this with a single expression inside a window function, eliminating the join, improving readability, and handling edge cases like missing rows or partition boundaries more cleanly.

The self-join approach requires knowing the key offset explicitly, fails gracefully only with careful NULL handling, and becomes difficult to read when multiple offsets are needed. LAG(revenue, 1, 0) in a single SELECT line communicates the intent directly: 'the previous row's revenue, defaulting to 0 if there is no previous row.' For sequential analysis — trends, differences, growth rates — offset functions turn a two-table join problem into a one-pass windowed scan.