Window Functions - Lead & Lag

This lesson on Window Functions - Lead & Lag is hands-on and example-driven. You will master the SQL LEAD and LAG window functions to perform row-to-row comparisons across sequential analytics data. You can access values from previous or subsequent rows, configure offsets and default fallback values, and apply partitioning to calculate period-over-period metrics cleanly without self-joins.

What You'll Be Able To Do

• Write queries using LAG to retrieve values from preceding rows for year-over-year metrics
• Implement LEAD to inspect subsequent row data within an ordered result set
• Apply custom offset arguments and default fallback values to eliminate unintended NULL results
• Combine PARTITION BY and ORDER BY clauses inside the OVER statement to scope row offsets across sub-groups
• Calculate period-over-period absolute and percentage variance directly within SQL queries

Detailed Concept Walkthrough

1. Row Comparison with LAG

The LAG function provides positional access to a preceding row's value relative to the current row within an ordered dataset. It allows you to place consecutive records side by side in the same output row without writing complex self-joins.

• Mechanism: LAG evaluates the specified column expression on the record that is $N$ positions before the current row within the window frame. If no prior row exists at that offset, it returns NULL by default.
• Under the Hood: The SQL engine sorts rows according to the ORDER BY specification inside the OVER clause and traverses the sorted partition linearly using a cursor to fetch the prior row's value.
• Syntax Rule: The basic syntax requires LAG(expression, [offset], [default]) OVER (ORDER BY column). The offset defaults to 1 if omitted, representing the immediately preceding row.

-- Retrieve current and prior year sales to compare growth
SELECT 
    order_year,
    sales,
    LAG(sales, 1) OVER (ORDER BY order_year) AS previous_year_sales,
    sales - LAG(sales, 1) OVER (ORDER BY order_year) AS yoy_growth
FROM yearly_sales;

Key Takeaway: LAG looks backward across the sorted window partition to fetch values from prior rows for period-over-period comparison.

2. Offsets and Default Value Handling

LAG and LEAD accept positional arguments that determine the look-back/look-forward step distance and supply fallback values for boundary rows.

• Offset Argument: Passing an integer as the second argument sets how many rows backward or forward to look. For example, an offset of 2 retrieves data from two rows prior (e.g., comparing 2020 directly to 2018).
• Default Fallback: The third argument specifies a replacement value when the offset lands outside partition boundaries. Setting this to 0 prevents NULL values on initial boundary rows.
• Best Practice: Always provide an explicit default value when downstream mathematical operations (such as subtraction or division) cannot tolerate NULL operands.

-- Offset by 2 years with a fallback default of 0
SELECT 
    order_year,
    sales,
    LAG(sales, 2, 0) OVER (ORDER BY order_year) AS sales_two_years_prior
FROM yearly_sales;

Key Takeaway: Using explicit offset and default parameters avoids unexpected NULL values in boundary rows during mathematical calculations.

3. Forward Row Inspection with LEAD

The LEAD function accesses values from rows that succeed the current row in the sort order. It acts as the exact directional inverse of LAG.

• Mechanism: LEAD projects forward into the dataset, returning a column value from $N$ rows ahead of the current evaluation record.
• Order Inversion Equivalence: A LEAD operation over an ascending sort is functionally equivalent to a LAG operation over a descending sort, demonstrating that row order determines directionality.
• Syntax Rule: The syntax mirrors LAG: LEAD(expression, [offset], [default]) OVER (ORDER BY column). Boundary conditions at the end of partitions return the default value or NULL.

-- Inspect next year sales alongside current sales
SELECT 
    order_year,
    sales,
    LEAD(sales, 1, 0) OVER (ORDER BY order_year) AS next_year_sales
FROM yearly_sales;

Key Takeaway: LEAD peeks ahead into subsequent rows and can be mirrored by inverting the ORDER BY clause with LAG.

4. Partitioned Window Scoping

Adding PARTITION BY divides the dataset into independent subsets where offset calculations start and reset per group.

• Mechanism: The window function executes independently across each group defined in PARTITION BY group_col. Offsets never bleed across group boundaries.
• Boundary Reset: The first record of every partition treats previous rows as out-of-bounds, correctly returning NULL or the assigned default value.
• Execution Flow: The query engine first partitions data by the designated column, sorts rows within each partition via ORDER BY, and finally evaluates the LEAD/LAG offset on each isolated subset.

-- Calculate prior year sales scoped independently to each region
SELECT 
    region,
    order_year,
    sales,
    LAG(sales, 1, 0) OVER (
        PARTITION BY region 
        ORDER BY order_year
    ) AS prev_year_region_sales
FROM regional_yearly_sales;

Key Takeaway: PARTITION BY isolates window offset evaluations so metrics reset cleanly across categorical boundaries.

Topics Covered in Window Functions - Lead & Lag

1. Introduction to Lead and Lag (0:00 - 0:45) — Overview of analytical window functions and their real-world importance for interview and operational analytics queries.
2. Dataset Aggregation and Setup (0:45 - 1:40) — Aggregating Superstore sales data by year to set up year-over-year comparison requirements.
3. Basic LAG Implementation (1:40 - 2:35) — Writing the basic LAG syntax with ORDER BY to fetch prior year sales.
4. Offset Arguments and Defaults (2:35 - 3:20) — Configuring offset steps and assigning a default value of zero to avoid NULLs.
5. LEAD Function Mechanics (3:20 - 4:15) — Demonstrating the LEAD function to fetch next-row values and illustrating equivalence via inverted sorting.
6. Partitioned Windows by Region (4:15 - 5:30) — Adding PARTITION BY region so look-back metrics reset cleanly per geographic grouping.

SQL Cheat Sheet

• LAG(col) — Fetches previous row value with default offset 1

  LAG(sales) OVER (ORDER BY order_year)
  

• LAG(col, offset) — Fetches value N rows prior within the partition

  LAG(sales, 2) OVER (ORDER BY order_year)
  

• LAG(col, offset, default) — Fetches prior value substituting out-of-bounds rows with default

  LAG(sales, 1, 0) OVER (ORDER BY order_year)
  

• LEAD(col, offset, default) — Fetches next row value substituting out-of-bounds rows

  LEAD(sales, 1, 0) OVER (ORDER BY order_year)
  

• OVER (PARTITION BY col ORDER BY col) — Scopes window offsets to independent category partitions

  LAG(sales, 1, 0) OVER (PARTITION BY region ORDER BY order_year)
  

Comparison Table

Common Pitfalls

• Mistake: Omitting the ORDER BY clause inside OVER when using LAG or LEAD. Avoid: Always include ORDER BY inside OVER to define deterministic sequential row ordering.
• Mistake: Forgetting PARTITION BY when calculating metrics across grouped categories. Avoid: Explicitly add PARTITION BY grouping_column to prevent lookups from crossing category boundaries.
• Mistake: Neglecting the default fallback value in arithmetic calculations. Avoid: Supply a numeric default like 0 to prevent calculations from returning NULL.

FAQs

• What happens if I omit the offset and default arguments in LAG or LEAD? The function defaults to an offset of 1 and returns NULL when an offset crosses the partition boundary.
• Can LEAD and LAG be used interchangeably? Yes, reversing the sort direction in the ORDER BY clause allows LEAD to replicate LAG and vice versa.
• Does PARTITION BY alter the final query output row count? No, window functions maintain the exact row count of the underlying query without collapsing rows like GROUP BY.