What are LEAD and LAG Functions in SQL?
Learn how SQL LAG and LEAD functions compare a row to its neighbors, with a month-over-month revenue example.
Expected Interview Answer
LAG and LEAD are offset window functions that let a row directly access a column's value from a preceding row (LAG) or a following row (LEAD) within the same ordered window, without a self-join.
Both functions take the target column, an optional offset (how many rows back or forward, defaulting to 1), and an optional default value to return when no such row exists, such as at the start or end of a partition. They are written with ORDER BY inside OVER(), and optionally PARTITION BY to keep comparisons within a group, like comparing a customer's orders only against that same customer's other orders. This makes LAG and LEAD the standard way to compute period-over-period differences, detect changes between consecutive rows, or build sequences like previous-value or next-value comparisons directly in SQL.
- Compares a row to its neighbor without a self-join
- Supports custom offsets and default values for missing neighbors
- Works per partition for grouped comparisons
- Simplifies month-over-month or day-over-day difference queries
AI Mentor Explanation
Think of a batter reviewing their innings-by-innings scores and wanting to see, next to each innings, the score from their previous innings to spot a form trend. LAG pulls that previous innings' score directly onto the current row, and LEAD would instead pull the next innings' score. Without these functions, a scorer would have to manually cross-reference a separate row for every comparison, which SQL avoids entirely with a built-in offset lookup.
Step-by-Step Explanation
Step 1
Choose LAG or LEAD
Use LAG to look backward at a preceding row, or LEAD to look forward at a following row.
Step 2
Specify the column and offset
Pass the target column and, optionally, how many rows back or forward to look (default is 1).
Step 3
Provide a default value
Supply a third argument to return when no such neighboring row exists, avoiding a NULL result.
Step 4
Add ORDER BY and PARTITION BY
Define the row sequence with ORDER BY, and optionally PARTITION BY to keep comparisons within a group.
What Interviewer Expects
- Correct distinction between LAG (previous row) and LEAD (next row)
- Knowledge of the offset and default-value arguments
- Awareness that ORDER BY inside OVER() defines what "previous" and "next" mean
- A concrete use case such as month-over-month comparison
Common Mistakes
- Mixing up LAG and LEAD directionality
- Forgetting to handle NULL results at partition boundaries with a default value
- Omitting ORDER BY, which makes the row sequence undefined
- Using a self-join instead of LAG or LEAD for a simple neighbor comparison
Best Answer (HR Friendly)
“LAG lets a row see a value from the row before it, and LEAD lets a row see a value from the row after it, both based on whatever ORDER BY you define. I use them most often to compute things like month-over-month growth without having to join a table to itself.”
Code Example
SELECT
month,
revenue,
LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS revenue_change
FROM MonthlyRevenue;
-- LAG pulls the previous row's revenue onto the current row,
-- defaulting to 0 for the first month where no prior row exists.Follow-up Questions
- How do you handle the NULL that LAG or LEAD returns at a partition boundary?
- How would you combine LAG and PARTITION BY to compare rows within the same customer only?
- Can LAG look back more than one row, and how do you specify that?
- How would you detect a status change between consecutive rows using LAG?
MCQ Practice
1. What does the LAG function return for a given row?
LAG accesses a column value from a row before the current row, based on the ORDER BY defined in the OVER clause.
2. What is the purpose of the third argument in LAG(column, offset, default)?
The default argument is returned when the offset points beyond the available rows, such as at the start of a partition.
3. Which function would you use to compare a row to the row that comes right after it?
LEAD accesses a value from a following row relative to the current row, based on the defined row order.
Flash Cards
What does LAG return? — A column value from a preceding row within the ordered window.
What does LEAD return? — A column value from a following row within the ordered window.
What is the offset argument for? — It specifies how many rows back or forward to look, defaulting to 1.
Why use a default value with LAG or LEAD? — To avoid NULL results when no neighboring row exists, such as at a partition boundary.