Introduction: Comparing Sequential Periods
Suppose you have monthly revenue figures:
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)
LAG(expression, [offset], [default]) accesses data from a preceding row at a specified physical offset prior to the current position within the window partition.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)
LEAD(expression, [offset], [default]) accesses data from a subsequent row at a specified physical offset following the current position within the window partition.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 |
LAG()
← Look Backward
CURRENT ROW
Evaluating
LEAD()
Look Forward →
The Critical Importance of ORDER BY
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):
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:
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:
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:
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:
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:
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
Omitting ORDER BY results in non-deterministic, random row comparisons.
Failing to wrap the denominator in NULLIF(..., 0) when prior values can be zero.
Accidentally comparing Customer B's first purchase against Customer A's final purchase.
Believing LEAD() predicts future events rather than reading following rows already stored in the table.
Practical Time-Series Exercises
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;
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.