LEAD & LAG
LAG and LEAD access values from previous and next rows within a window - without a self-join. LAG looks back, LEAD looks forward. They are essential for calculating period-over-period changes, differences between consecutive rows, and detecting trends.
LEAD & LAG Syntax
- LAG(col, offset, default) OVER (ORDER BY col) - value from previous row
- LEAD(col, offset, default) OVER (ORDER BY col) - value from next row
- offset defaults to 1 (immediately previous/next row)
- default - value when no previous/next row exists (else NULL)
- Often combined with PARTITION BY for per-group comparisons
- Classic use: month-over-month change = current - LAG(current)
LEAD & LAG Examples
-- Month-over-month payment comparison
SELECT
TO_CHAR(paid_at, 'YYYY-MM') AS month,
SUM(amount) AS revenue,
LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(paid_at, 'YYYY-MM')) AS prev_month,
SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY TO_CHAR(paid_at, 'YYYY-MM')) AS change
FROM payments
WHERE paid_at IS NOT NULL
GROUP BY TO_CHAR(paid_at, 'YYYY-MM')
ORDER BY month;
-- Consecutive order analysis per user
SELECT
user_id,
id AS order_id,
created_at,
total_amount,
LAG(total_amount, 1, 0) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS prev_order_amount,
total_amount - LAG(total_amount, 1, 0) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS change_from_prev
FROM orders;
-- LEAD: peek at next order
SELECT
id,
user_id,
status,
LEAD(status) OVER (PARTITION BY user_id ORDER BY created_at) AS next_status
FROM orders;
-- Days between consecutive orders
SELECT
user_id,
created_at,
LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_date,
created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS days_between
FROM orders;Quick Quiz
Tip
Tip
Practice LEAD LAG in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with LEAD LAG is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of LEAD LAG from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- LAG and LEAD access values from previous and next rows within a window - without a self-join.
- LAG(col, offset, default) OVER (ORDER BY col) - value from previous row
- LEAD(col, offset, default) OVER (ORDER BY col) - value from next row
- offset defaults to 1 (immediately previous/next row)