LAG() — looking at the previous row
LAG() reaches backward from the current row, based on the ordering defined inside OVER(), and returns a value from that earlier row.
SELECT order_date, total,
LAG(total) OVER (ORDER BY order_date) AS prev_total
FROM daily_sales;| order_date | total | prev_total |
|---|---|---|
| Jan 1 | 500 | NULL |
| Jan 2 | 620 | 500 |
| Jan 3 | 580 | 620 |
Every row gets the previous row's total attached to it — except the very first row, which has nothing before it (more on that in the default value section).
LEAD() — looking at the next row
LEAD() is the mirror image of LAG() — it reaches forward instead of backward.
SELECT order_date, total,
LEAD(total) OVER (ORDER BY order_date) AS next_total
FROM daily_sales;| order_date | total | next_total |
|---|---|---|
| Jan 1 | 500 | 620 |
| Jan 2 | 620 | 580 |
| Jan 3 | 580 | NULL |
Same mechanism, opposite direction — the last row has nothing after it, so its next_total is NULL. For the wider family of window functions these belong to, see Window Functions Explained Simply.
The offset argument
Both functions accept a second argument: how many rows back (for LAG) or forward (for LEAD) to reach. It defaults to 1 if omitted.
SELECT order_date, total,
LAG(total, 1) OVER (ORDER BY order_date) AS prev_1_day,
LAG(total, 7) OVER (ORDER BY order_date) AS prev_7_days
FROM daily_sales;LAG(total, 7) is the standard way to express "compare to the same day last week" once the data is ordered daily — no need for a self-join with a computed date offset in the join condition.
The default value argument
A third argument sets what to return instead of NULL when there's no row to look at — useful when a downstream calculation would otherwise need to handle NULL as a special case.
SELECT order_date, total,
LAG(total, 1, 0) OVER (ORDER BY order_date) AS prev_total
FROM daily_sales;
-- first row's prev_total is 0 instead of NULL0 as a stand-in for "no previous day" is fine for a change calculation, but it can be misleading if 0 is also a legitimate value the column could actually hold — in that case, leaving the default as NULL and handling it explicitly downstream is safer. See NULL Handling in SQL for the underlying reasoning.
Combining LAG/LEAD with PARTITION BY
Without PARTITION BY, LAG() and LEAD() operate across the entire ordered result set — which means a row can reach into a different customer's or category's data at a boundary. Adding PARTITION BY resets the look-back at the start of every group.
SELECT customer_id, order_date, total,
LAG(total) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS prev_order_total
FROM orders;| customer_id | order_date | total | prev_order_total |
|---|---|---|---|
| 101 | Jan 1 | 90 | NULL |
| 101 | Jan 9 | 60 | 90 |
| 102 | Jan 3 | 150 | NULL |
Customer 102's first order correctly shows NULL instead of reaching back into customer 101's last order — PARTITION BY is what keeps the comparison confined to each customer's own order history. This is the same partitioning mechanism covered in full in PARTITION BY Explained in SQL.
Example: month-over-month change
A standard business reporting pattern — compute the change and percentage change from the previous period in a single query:
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1
) AS pct_change
FROM monthly_revenue;| month | revenue | change | pct_change |
|---|---|---|---|
| Jan | 10,000 | NULL | NULL |
| Feb | 11,500 | 1,500 | 15.0 |
| Mar | 10,800 | -700 | -6.1 |
Before window functions, this required a self-join keyed on a computed "previous month" value — fragile the moment a month is missing entirely. LAG() just uses whatever row actually comes before it in the defined order, regardless of gaps.
Example: flagging a gap in sequential data
LAG() is also the standard way to detect missing entries in a sequence, such as a skipped invoice number or a missing day in a daily log:
SELECT invoice_id,
invoice_id - LAG(invoice_id) OVER (ORDER BY invoice_id) AS gap
FROM invoices;
-- any gap other than 1 marks a missing or deleted invoice numberWhy LAG/LEAD beats a self-join
Before window functions were widely available, comparing a row to its predecessor meant a self-join: alias the table twice, and join row B to row A where B's key is one greater than A's, or where B's date is the closest date after A's. That join condition is exactly where gaps and duplicate values cause trouble — a missing invoice number breaks a +1 join condition outright, and duplicate timestamps make "the next row" ambiguous. LAG() and LEAD() sidestep the join condition entirely by working directly against the row order established by ORDER BY. See the self-join pitfalls directly in SQL Tricky Interview Questions With Answers.
Key takeaways
- LAG() reaches backward, LEAD() reaches forward, both relative to ORDER BY inside OVER().
- The second argument is the offset (default 1); the third is the default value (default NULL).
- PARTITION BY resets LAG/LEAD at the start of each group, preventing cross-group leakage.
- Month-over-month and gap-detection queries are the two most common real-world LAG patterns.
- LAG/LEAD replace a self-join with no join condition to get wrong on gaps or duplicates.
Frequently asked questions
What is the difference between LAG and LEAD in SQL?
LAG() looks backward and returns a value from an earlier row relative to the current one, based on the ORDER BY inside OVER(). LEAD() looks forward and returns a value from a later row. Both default to looking exactly one row away unless a different offset is specified.
What does the second argument to LAG or LEAD do?
The second argument is the offset — how many rows back or forward to look. LAG(total, 2) looks two rows back instead of one. Omitting it defaults to an offset of 1.
What happens when LAG or LEAD has no row to look at?
At the boundaries of the result set — the first row for LAG, the last row for LEAD — there is no previous or next row to return, so the function returns NULL by default. A third argument can be supplied to return a custom default value instead of NULL.
How do LAG and LEAD behave with PARTITION BY?
Adding PARTITION BY resets LAG and LEAD at the start of every partition, so the first row of each partition returns NULL (or the default) for LAG instead of reaching back into the previous partition's data, keeping comparisons confined to their own group.
Why use LAG instead of a self-join to compare a row to the previous one?
A self-join requires matching each row to its predecessor through a join condition, which gets error-prone with gaps or ties in the ordering column, and typically costs more to execute. LAG expresses the same intent directly against the row order, with no join condition to get wrong.