Course contentsShow
← Data Science
Lesson 1023 of 2,14526. Advanced SQLFree lesson

Introduction to Window Functions: LAG and LEAD

Understand LAG and LEAD as window functions that access data from other rows relative to the current row.

Introduction to Window Functions: LAG and LEAD

What you'll learn: How to access data from previous or following rows relative to your current row using LAG and LEAD window functions.

The Core Idea

Imagine you're reading through a list of daily sales figures, and for each day, you want to compare today's sales to yesterday's—or tomorrow's. In normal SQL, you'd need complicated self-joins to connect each row to its neighbor. LAG and LEAD window functions make this trivial by letting you "peek" at other rows directly.

LAG looks backward to access a previous row's value.
LEAD looks forward to access a following row's value.

Both functions respect the ordering you specify in the ORDER BY clause within the OVER() clause and can be partitioned just like other window functions you've learned.

Basic Syntax

LAG(column_name, offset, default_value) OVER (ORDER BY sort_column)
LEAD(column_name, offset, default_value) OVER (ORDER BY sort_column)
  • column_name: Which column's value to retrieve
  • offset: How many rows back/forward (defaults to 1)
  • default_value: What to return when there's no previous/next row (defaults to NULL)

Common Use Cases

  • Calculating differences: Compare current vs previous values (sales growth, price changes)
  • Identifying trends: Detect consecutive increases or decreases
  • Filling gaps: Use LEAD to preview the next non-null value
  • Sequential analysis: Analyze patterns across adjacent time periods

Key Takeaway: LAG and LEAD provide row-relative access within your result set, eliminating the need for complex self-joins when comparing or analyzing sequential data.