Skip to content

Chapter 3 of 6

Value and Offset Functions

The value family — LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE — fetches values from other rows relative to the current one within the same partition. LAG(col, n, default) returns the value from n rows before the current row, with a default offset of 1 (the immediately previous row), and returns NULL or the supplied default when no such row exists in the partition. LEAD is its symmetric counterpart, looking forward in the ordering. Together they are the workhorses for computing differences between adjacent rows, year-over-year deltas via LAG(amount, 12) OVER (PARTITION BY metric ORDER BY month), percent change between consecutive prices, days since the previous order per customer, and similar adjacent-row comparisons.

FIRST_VALUE and LAST_VALUE pick from the boundaries of the partition or frame and share similar semantics. A notorious gotcha: plain LAST_VALUE(x) OVER (PARTITION BY g ORDER BY ts) returns the current row's value rather than the partition's last value, because the default frame ends at CURRENT ROW. The fix is to explicitly set the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. LAST_VALUE combined with IGNORE NULLS and a cumulative frame is the standard trick for "carry forward" or "forward-fill" semantics, where missing values should be replaced by the most recent non-null value. NTH_VALUE(col, n) retrieves the n-th value in the frame, useful for picking a specific peer position such as the third-highest score.

NULL handling deserves special attention. The LAG(col, n, default) signature lets you supply a fallback for missing rows, but this is a positional, constant replacement. For more flexible NULL suppression when picking a value, IGNORE NULLS is available in PostgreSQL 16+, BigQuery, Snowflake, Redshift, SQL Server, and Oracle (with limited support in MySQL until 8.0.22), and skips NULLs when selecting a value, while RESPECT NULLS treats NULL as a valid value. When ordering within the window, appending NULLS FIRST or NULLS LAST controls where NULLs land in the frame, which matters for ranking positions, frame boundaries, and the behavior of cumulative aggregates when NULLs are present.

All chapters
  1. 1Foundations of Window Functions
  2. 2Ranking Functions
  3. 3Value and Offset Functions
  4. 4Aggregate Windows and Running Calculations
  5. 5Advanced Analytical Patterns
  6. 6Performance, Engines, and Practical Tips

Drill it

Reading is not remembering. These come from the SQL Window Functions Cheatsheet deck:

Q

Running total of <i>amount</i> by <i>user_id</i> ordered by <i>ts</i>?

SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING)

Q

Difference between ROW_NUMBER, RANK, DENSE_RANK?

ROW_NUMBER: unique sequential.RANK: same value → same rank, leaves gaps.DENSE_RANK: same value → same rank, no gaps.

Q

Top N rows per group?

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY score DESC) rn FROM t) x WHERE rn &lt;= 3;

Q

Previous row's value in same partition?

LAG(price) OVER (PARTITION BY symbol ORDER BY ts)

Study all 223 cards → Export to Anki