SQL LAG and LEAD Functions
intermediatewindow-functions
2 min read
What are LAG and LEAD?
LAG accesses a value from a previous row. LEAD accesses a value from a subsequent row. Both use the ORDER BY within the OVER clause.
Syntax
LAG(column, offset, default) OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)offset— How many rows back (LAG) or forward (LEAD). Default is 1.default— Value to return when there is no row at the offset.
When to Use
- Calculating day-over-day changes
- Comparing each order to the previous one
- Detecting status transitions
- Computing time between events
Key Points
- Offset — The default offset is 1. You can specify any positive integer.
- Default Value — Provide a default to avoid NULLs at the edges:
LAG(total, 1, 0). - PARTITION BY — Partition to reset the window per group.
- ORDER BY Required — LAG and LEAD require ORDER BY in the OVER clause.
- No Aggregation — They return a single value from a specific row, not an aggregate.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL LAG and LEAD Functions Challenge
Write a query that solve this task: show each order alongside the previous order total.
Expected result
Each order with the previous order total and the difference.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are LAG and LEAD?
Initializing database...Each run starts from fresh sample data.
More Examples
Next enrollment date
Show the date of the next enrollment for the same student.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What does LAG return for the first row?
The first row has no previous row, so LAG returns NULL by default. Provide a third argument for a default: LAG(column, 1, 0).
Can I use LAG with PARTITION BY?
Yes. LAG with PARTITION BY looks at the previous row within the same partition.