SQL LAG() & LEAD() – Guided Study Workspace

Introduction: Comparing Sequential Periods

Suppose you have monthly revenue figures:

Jan: $10,000 • Feb: $12,000 (+2k) • Mar: $9,000 (-3k) • Apr: $15,000 (+6k)

How can you compare each month's revenue against the previous month, or inspect the following month, without writing clumsy self joins?

LAG(): Look backward ➔ Fetch the previous row's value.
LEAD(): Look forward ➔ Fetch the next row's value.

What Is LAG()? (Looking Backward)

Core Definition

LAG(expression, [offset], [default]) accesses data from a preceding row at a specified physical offset prior to the current position within the window partition.

SELECT
    month,
    sales,
    LAG(sales) OVER (
        ORDER BY month
    ) AS previous_sales
FROM monthly_sales;

Month	Sales	LAG(sales) Output	Explanation
Jan	$10,000	NULL	First row has no preceding record
Feb	$12,000	$10,000	Retrieved from Jan
Mar	$9,000	$12,000	Retrieved from Feb
Apr	$15,000	$9,000	Retrieved from Mar

What Is LEAD()? (Looking Forward)

Core Definition

LEAD(expression, [offset], [default]) accesses data from a subsequent row at a specified physical offset following the current position within the window partition.

SELECT
    month,
    sales,
    LEAD(sales) OVER (
        ORDER BY month
    ) AS next_sales
FROM monthly_sales;

Month	Sales	LEAD(sales) Output	Explanation
Jan	$10,000	$12,000	Retrieved from Feb
Feb	$12,000	$9,000	Retrieved from Mar
Mar	$9,000	$15,000	Retrieved from Apr
Apr	$15,000	NULL	Final row has no following record

Diagram 1: Directional Row Access Model

Previous Row
LAG()
← Look Backward

Active Record
CURRENT ROW
Evaluating

Next Row
LEAD()
Look Forward →

The Critical Importance of ORDER BY

Crucial Rule: Ordering Establishes Meaning

Relational tables are unordered bags of rows. ORDER BY inside OVER() is mandatoryto define the exact chronological or numeric sequence of "previous" and "next".

Offsets: Looking N Rows Earlier or Later

By default, LAG(col) looks 1 row backward. You can specify a custom offset (e.g. LAG(sales, 2) to look 2 rows backward):

SELECT
    month,
    sales,
    LAG(sales, 2) OVER (ORDER BY month) AS two_months_ago
FROM monthly_sales;

Default Fallback Values (Replacing Boundary NULLs)

You can supply a third parameter as a fallback default when the requested row falls outside boundary bounds:

-- Returns 0 instead of NULL on the first row
SELECT month, sales,
LAG(sales, 1, 0) OVER (ORDER BY month) AS previous_sales_safe
FROM monthly_sales;

Calculating Period-over-Period Absolute Change

Subtracting the previous value from the current value yields Month-over-Month (MoM) dollar variance:

SELECT
    month,
    sales,
    sales - LAG(sales) OVER (ORDER BY month) AS mom_sales_change
FROM monthly_sales;

Calculating Percentage Change & NULLIF Zero-Division Protection

To calculate percentage growth while protecting against division-by-zero errors:

SELECT
    month,
    sales,
    (
        (sales - LAG(sales) OVER (ORDER BY month))
        /
        NULLIF(LAG(sales) OVER (ORDER BY month), 0)
    ) * 100.0 AS pct_growth
FROM monthly_sales;

LAG() With PARTITION BY: Group-Level Trend Resets

Adding PARTITION BY region ensures sequential comparisons reset per region:

SELECT
    region,
    month,
    sales,
    LAG(sales) OVER (
        PARTITION BY region
        ORDER BY month
    ) AS prev_month_regional_sales
FROM regional_sales;

LEAD() With PARTITION BY

Similarly, LEAD() inspects following values strictly within the same customer or regional group:

SELECT
    customer_id,
    order_date,
    LEAD(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS next_order_date
FROM customer_orders;

Real-World Data Analytics Use Cases

Month-over-Month Revenue Growth: Tracking monthly executive business trajectory.
Customer Purchase Intervals: Measuring days elapsed between repeat orders (order_date - LAG(order_date)).
Stock & Crypto Volatility: Daily closing price fluctuations against previous close.
Employee Salary Progression: Comparing promotional salary increases over time.

LAG() vs. Self JOINs

Before window functions existed, sequential row comparisons required joining a table to itself on t1.id = t2.id + 1 or complex correlated subqueries. LAG() expresses this analytical intent cleanly and directly.

Intentional Boundary NULL Handling

Boundary NULLs are semantically accurate in data analytics: the very first month has no prior month to compare against. Only replace NULL with 0 if 0 represents an accurate business baseline.

Common LAG() & LEAD() Mistakes to Avoid

1. Forgetting ORDER BY

Omitting ORDER BY results in non-deterministic, random row comparisons.

2. Dividing by Zero in % Change

Failing to wrap the denominator in NULLIF(..., 0) when prior values can be zero.

3. Forgetting PARTITION BY

Accidentally comparing Customer B's first purchase against Customer A's final purchase.

4. Confusing LEAD with AI Forecasting

Believing LEAD() predicts future events rather than reading following rows already stored in the table.

Practical Time-Series Exercises

Exercise 1: Customer Repeat Purchase Interval (Days Between Orders)

SELECT
    customer_id,
    order_date,
    amount,
    LAG(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS prev_order_date,
    order_date - LAG(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS days_since_last_purchase
FROM orders;

⚡Live Interactive SQL LAG() & LEAD() Practice Lab

Test backward & forward row lookups, configure offsets & default fallbacks, and compute live MoM growth

Hover over any row below to watch how LAG() accesses the row above and LEAD() accesses the row below:

Month	Sales	LAG(sales) [Previous]	LEAD(sales) [Next]
2026-01 (Jan)	$10,000	NULL (Boundary)	$12,000
2026-02 (Feb)	$12,000	$10,000	$9,000
2026-03 (Mar)	$9,000	$12,000	$15,000
2026-04 (Apr)	$15,000	$9,000	$18,000
2026-05 (May)	$18,000	$15,000	$14,000
2026-06 (Jun)	$14,000	$18,000	NULL (Boundary)

SQL LAG() & LEAD() Best Practices

Always Define Explicit ORDER BY: Prevents non-deterministic, random row lookups.
Use NULLIF on Denominators: Protect percentage growth queries against division-by-zero crashes.
Use PARTITION BY on Entity Time-Series: Prevent cross-boundary data leakage between customers or regions.
Remember That LEAD Is Not Forecasting: LEAD merely looks forward into existing dataset rows.

What You Should Know Now: Checklist

✓LAG() Concept: Accesses preceding row values (looks backward).
✓LEAD() Concept: Accesses following row values (looks forward).
✓Boundary Behavior: First row of LAG is NULL; last row of LEAD is NULL.
✓Offsets & Defaults: LAG(val, offset, default).
✓Period-over-Period: Subtracting LAG values computes variance and percentage growth.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL LAG() & LEAD()

Test your understanding of backward/forward row lookups, ORDER BY importance, offsets, default values, and MoM growth calculations.

Question 1 of 8Current Score: 0 / 0

What is the primary role of the LAG() window function in SQL?