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.