LAG/LEAD — Use LAG/LEAD for comparisons across rows |...

SECTION 1

Historical Context & Motivation

Relational databases, grounded in E. F. Codd's 1970 relational model, were originally designed to treat each row as an independent tuple within a set. This set-oriented paradigm was extraordinarily powerful for filtering and aggregating data, yet it made one class of questions surprisingly awkward: comparisons between adjacent rows. Analysts frequently needed to compute differences between consecutive time periods — month-over-month revenue changes, day-over-day stock price movements, or sequential event gaps in log data. Before window functions existed, the standard approach required self-joins or correlated subqueries, both of which were verbose, error-prone, and often carried significant performance penalties as table sizes grew.

1992

SQL-92 Standard

The SQL-92 standard solidified JOIN syntax and subquery support, but offered no native mechanism for ordered row-to-row access. Analysts resorted to self-joins keyed on sequential identifiers to compare adjacent rows.

1999

SQL:1999 — Window Functions Proposed

The SQL:1999 standard introduced the concept of window functions (also called analytic or OLAP functions), including OVER clauses, PARTITION BY, and ORDER BY within window specifications. This laid the theoretical groundwork for ordered computations.

2003

SQL:2003 — LAG and LEAD Standardized

The SQL:2003 revision formally standardized LAG and LEAD as offset functions, enabling direct access to preceding and following rows within a defined window partition and order.

2005–2012

Major RDBMS Adoption

Oracle, PostgreSQL, SQL Server, and eventually MySQL (8.0, 2018) implemented LAG/LEAD. Adoption accelerated as data warehousing and time-series analysis became ubiquitous in industry.

2020s

Cloud Analytics Era

Modern cloud warehouses like BigQuery, Snowflake, and Redshift treat LAG/LEAD as foundational primitives. They appear routinely in analytics engineering pipelines managed by tools like dbt.

The fundamental question that LAG and LEAD address is deceptively simple: how can we reference the value of another row, positioned at a known offset in a defined ordering, without leaving the current row's context? Before these functions existed, answering this question required joining a table to itself — a technique that scales poorly and obscures intent. LAG and LEAD replaced an entire class of self-join patterns with a single, declarative expression.

SECTION 2

Core Principles & Definitions

LAG and LEAD belong to the family of window offset functions. Unlike aggregate window functions such as SUM() OVER(...) or AVG() OVER(...), which collapse multiple rows into a single value, offset functions retrieve a scalar value from a specific relative position in the ordered partition. Understanding their behavior requires grasping several foundational concepts that govern all window functions.

Window Partition

The PARTITION BY clause divides the result set into independent groups. LAG/LEAD never cross partition boundaries — if no partition is specified, the entire result set forms a single partition.

Window Ordering

The ORDER BY inside the OVER clause defines the logical sequence of rows. This ordering determines which row is 'previous' (for LAG) and which is 'next' (for LEAD). Without ORDER BY, results are non-deterministic.

Offset Parameter

The second argument specifies how many rows back (LAG) or forward (LEAD) to reach. It defaults to 1. An offset of 0 returns the current row's value, effectively acting as an identity function.

Default Value

The optional third argument supplies a fallback when the offset extends beyond the partition boundary. Without it, the function returns NULL for edge rows — the first row for LAG(…, 1) and the last row for LEAD(…, 1).

Non-Aggregating Behavior

Unlike SUM or COUNT windows, LAG/LEAD do not reduce rows. Every input row produces exactly one output row. The result set cardinality remains unchanged, preserving granularity.

✦ KEY TAKEAWAY

Think of LAG and LEAD like reading a book while keeping a finger on the current page. LAG lets you flip back a few pages to recall what happened earlier, while LEAD lets you peek ahead at what comes next — all without losing your current place. The PARTITION BY clause acts as separate chapters; you can only flip within the chapter you're reading, never across chapter boundaries.

SECTION 3

Visual Explanation

The diagram above shows a five-row monthly revenue table. The violet arrows trace how LAG reaches backward to the previous row, while the cyan arrows show LEAD reaching forward. Row 3 (March) is highlighted in amber as the current evaluation context. Notice that boundary rows return NULL when the offset extends beyond the partition.

The visual makes a critical point: LAG and LEAD operate within the context of each row independently. As the database engine evaluates row 3, it simultaneously has access to the value from row 2 (via LAG) and row 4 (via LEAD). This is fundamentally different from a self-join, where the engine must match rows explicitly via a JOIN predicate. The window function approach lets the query planner optimize the scan pattern — often requiring only a single pass over the sorted data rather than the nested-loop or hash-match patterns that self-joins may trigger.

SECTION 4

Syntax & Mechanism

General Syntax

LAG SYNTAX

LAG(expression, offset, default) OVER (PARTITION BY col ORDER BY col)

expression — the column or calculation to retrieve from a prior row. offset — a non-negative integer indicating how many rows back (default 1). default — the value returned when the offset falls outside the partition boundary (default NULL).

LEAD SYNTAX

LEAD(expression, offset, default) OVER (PARTITION BY col ORDER BY col)

Identical signature to LAG, except the offset points forward in the ordered partition rather than backward. LEAD(..., 1) returns the value from the next row; LEAD(..., 2) skips one row ahead.

Common Derived Patterns

ABSOLUTE CHANGE

revenue − LAG(revenue, 1, 0) OVER (ORDER BY month)

Computes the difference between the current row's revenue and the previous row's revenue. The default of 0 means the first row reports its full revenue as the 'change', which may or may not be semantically appropriate depending on context.

PERCENTAGE CHANGE

(revenue − LAG(revenue,1)) × 100.0 / LAG(revenue,1)

Yields the percent change from the prior period. Guard against division by zero with NULLIF or a CASE expression when the lagged value could be zero.

It is worth noting that LAG and LEAD are duals of each other. Formally, LAG(x, n) evaluated at row i returns the same value as LEAD(x, n) evaluated at row i − n. You can always rewrite one in terms of the other by adjusting the offset direction. In practice, choose whichever reads more naturally: LAG when reasoning about 'what came before' and LEAD when reasoning about 'what comes next.'

⚠️ Determinism Warning

If your ORDER BY clause does not produce a unique ordering (i.e., ties exist), LAG and LEAD may return different results across executions. Always ensure that the window ORDER BY resolves ties — add a tiebreaker column such as a primary key — or accept that the result is intentionally non-deterministic.

SECTION 5

Detailed Use Cases & Classification

LAG and LEAD surface in a wide variety of analytical scenarios. Below we classify the most common patterns, illustrate how partitioning changes the semantics, and provide a second visual diagram showing a partitioned LAG operation where comparisons are scoped to independent groups.

The upper half shows correct behavior: two partitions (Engineering and Marketing) compute LAG independently. The lower half demonstrates the cross-partition leakage bug that occurs when PARTITION BY is omitted — Carol's LAG value erroneously comes from Alice's row in a different department.

Common LAG/LEAD application patterns

Use Case	Function	Example Expression
Month-over-month change	`LAG`	`revenue - LAG(revenue,1) OVER (ORDER BY month)`
Day-over-day % change	`LAG`	`(price - LAG(price)) * 100.0 / LAG(price) OVER (ORDER BY date)`
Time between events	`LAG`	`event_ts - LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts)`
Forward-looking forecast comparison	`LEAD`	`LEAD(actual_sales,1) OVER (ORDER BY week) - forecast`
Sessionization (gap detection)	`LAG`	`CASE WHEN ts - LAG(ts) OVER (...) > INTERVAL '30 min' THEN 1 ELSE 0 END`

SECTION 6

Worked Example — Month-over-Month Revenue Growth

Suppose we have a table monthly_revenue with columns region, month_date, and revenue. We want to compute the absolute and percentage change in revenue from the prior month, independently for each region, and handle the first month gracefully.

Computing Month-over-Month Revenue Growth per Region

Step 1 — Identify the Window SpecificationWe need comparisons within each region independently, so we use PARTITION BY region. The chronological ordering is defined by ORDER BY month_date. Together, this ensures LAG reaches back to the same region's prior month.

OVER (PARTITION BY region ORDER BY month_date)

Step 2 — Write the LAG ExpressionWe call LAG with offset 1 (default) and no explicit default, accepting NULL for the first month in each partition. We alias this as prev_revenue for clarity.

LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month_date) AS prev_revenue

Step 3 — Compute Absolute ChangeThe absolute change is simply the current revenue minus the previous revenue. Because the first row in each partition has a NULL lag, this expression evaluates to NULL for the first month — which is the correct semantic result.

revenue - LAG(revenue,1) OVER (...) AS abs_change

Step 4 — Compute Percentage ChangeWe divide the absolute change by the previous revenue and multiply by 100. To protect against division by zero in case a previous month had zero revenue, we wrap the denominator in NULLIF. We round to two decimal places for readability.

ROUND((revenue - LAG(revenue,1) OVER (...)) * 100.0 / NULLIF(LAG(revenue,1) OVER (...), 0), 2) AS pct_change

Step 5 — Assemble the Final QueryPutting it all together into a complete SELECT statement, we include region and month_date for context, the raw revenue, and the two derived columns.

SELECT region, month_date, revenue, LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month_date) AS prev_revenue, revenue - LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month_date) AS abs_change, ROUND((revenue - LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month_date)) * 100.0 / NULLIF(LAG(revenue, 1) OVER (PARTITION BY region ORDER BY month_date), 0), 2) AS pct_change FROM monthly_revenue ORDER BY region, month_date;

💡 Optimization Tip: Named Window Clause

When referencing the same window specification multiple times, most modern engines support the WINDOW clause to avoid repetition: WINDOW w AS (PARTITION BY region ORDER BY month_date) — then use LAG(revenue, 1) OVER w throughout the query. This improves readability and can help the optimizer recognize shared sort operations.

SECTION 7

LAG/LEAD vs. Alternative Approaches

Before LAG and LEAD became available, SQL developers relied on self-joins or correlated subqueries to achieve the same result. Understanding the tradeoffs between these approaches highlights why window offset functions have become the preferred idiom in modern SQL development.

Comparison of row-offset techniques in SQL

Criterion	LAG/LEAD	Self-Join	Correlated Subquery
Readability	High — declarative intent is explicit in the function name.	Moderate — requires understanding the join predicate logic.	Low — nested subqueries obscure the comparison intent.
Performance	Generally O(n log n) dominated by the sort; single pass after sorting.	May require O(n²) nested-loop or additional index lookups.	Often O(n²) due to per-row subquery execution.
NULL handling at boundaries	Automatic NULL or user-defined default via third parameter.	Requires LEFT JOIN to preserve boundary rows.	Returns NULL naturally but requires explicit NOT EXISTS guards.
Maintainability	Changing the offset is a single integer edit.	Changing the offset requires restructuring the ON clause.	Requires modifying the WHERE clause of the subquery.
Multi-offset access	Add another LAG/LEAD call; shares the same sort.	Requires an additional self-join per offset — combinatorial growth.	Requires an additional correlated subquery per offset.

✦ KEY TAKEAWAY

LAG and LEAD are to row-offset comparisons what array indexing is to data structure access. Just as arr[i-1] in a programming language gives you O(1) access to the previous element without searching, LAG provides O(1) access (post-sort) to the previous row without an explicit join. The self-join approach is analogous to scanning the array looking for a match — correct but unnecessarily expensive. Whenever you find yourself writing a self-join to compare adjacent rows, consider it a strong signal that LAG or LEAD is the more idiomatic and efficient solution.

SECTION 8

Connection to Advanced Window Concepts

LAG and LEAD are point-access functions — they retrieve a single value at a fixed offset. This places them within a broader taxonomy of window functions that includes range-based aggregations (using ROWS or RANGE frame clauses), ranking functions (ROW_NUMBER, RANK, DENSE_RANK), and first/last value functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE). Understanding how these relate helps you select the right tool for each analytical question.

LAG/LEAD in the window function taxonomy

Feature	LAG / LEAD	FIRST_VALUE / LAST_VALUE	SUM OVER (ROWS BETWEEN ...)
Access pattern	Single row at fixed relative offset	Single row at partition start or end	Aggregate over a sliding frame of rows
Typical use	Period-over-period comparison	Baseline comparison (current vs. first entry)	Rolling averages, cumulative sums
Frame clause required?	No — offset is an argument, not a frame	Technically yes, but often defaults suffice	Yes — frame boundaries define the aggregation range
Output cardinality	One value per row (scalar)	One value per row (scalar)	One value per row (scalar aggregate)

As you advance in SQL analytics, you will encounter scenarios where LAG/LEAD alone are insufficient. For instance, computing a 7-day rolling average requires a frame-based aggregate rather than a point offset. Similarly, detecting the first occurrence of a threshold crossing within a partition calls for FIRST_VALUE with a filtered frame. Mastering LAG/LEAD provides an excellent foundation for these more general window patterns, because the partition-and-order mental model is shared across all window functions. The key extension is understanding that frames generalize the fixed-offset concept into a variable-width sliding window over the ordered partition.

🔗 Composability Note

LAG/LEAD results can be composed with other window functions in the same SELECT list. For example, you might compute revenue - LAG(revenue,1) OVER w alongside AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to juxtapose point-to-point change with a smoothed trend — all without subqueries or CTEs.

SECTION 9

Practice Problems

PROBLEM 1 — CONCEPTUAL

Explain why LAG(salary, 1) OVER (ORDER BY hire_date) returns NULL for the employee with the earliest hire_date. What mechanism in the SQL standard causes this behavior, and how would you change the function call to return 0 instead of NULL?

PROBLEM 2 — BASIC CALCULATION

Given a table daily_temps(city, obs_date, temp_f), write a query that returns each city, date, temperature, and the temperature from the previous day for that city. Name the derived column prev_day_temp.

PROBLEM 3 — INTERMEDIATE

Using the same daily_temps table, write a query that flags days where the temperature dropped by more than 10°F compared to the previous day. Return city, obs_date, temp_f, prev_day_temp, temp_change, and a boolean column cold_snap that is TRUE when the drop exceeds 10°F.

PROBLEM 4 — APPLIED

A web analytics table page_views(user_id, view_ts, page_url) records timestamped page visits. A session is defined as a sequence of page views where no gap between consecutive views exceeds 30 minutes. Write a query that assigns a session_id (as a running count of session boundaries) to each page view for each user.

PROBLEM 5 — CRITICAL THINKING

Consider a table stock_prices(ticker, trade_date, close_price) where multiple tickers share the same trade dates. A colleague writes: SELECT ticker, trade_date, close_price, LAG(close_price) OVER (ORDER BY trade_date) AS prev_close FROM stock_prices; Identify all semantic and correctness issues with this query. Propose a corrected version and explain why each change is necessary. Additionally, discuss under what circumstances the original query might appear to work correctly during testing but fail in production.

SUMMARY

Summary

LAG and LEAD are SQL window offset functions that provide direct access to values in preceding and subsequent rows within an ordered partition. They accept three arguments — an expression to evaluate, an offset (defaulting to 1), and an optional default value for boundary rows. The PARTITION BY clause scopes comparisons to logical groups, preventing cross-group data leakage, while ORDER BY within the OVER clause defines the row sequence and must produce a deterministic ordering to guarantee repeatable results.

These functions replace verbose self-join and correlated subquery patterns with concise, declarative expressions. Common applications include period-over-period change calculations, gap detection for sessionization, and time-between-events analysis. LAG and LEAD are duals of each other and serve as a gateway to the broader family of window functions including FIRST_VALUE, LAST_VALUE, and frame-based aggregates. Mastering them equips you with a foundational pattern that recurs throughout analytics engineering, data science, and backend development.