SQL LAG and LEAD Window Functions Explained

Your sales team wants to know whether each order was higher or lower than the one before it, sorted by date. That comparison requires seeing two rows at once: the current row and the previous one. Without LAG and LEAD, you'd need a self-join. With them, you write it in a single query.

What LAG and LEAD Do

LAG and LEAD are window functions that let you reference another row's value from within the current row. LAG looks backward. LEAD looks forward. Both use the sort order you define inside OVER.

The syntax for both follows the same pattern:

SELECT
    column_name,
    LAG(column_name, offset, default) OVER (ORDER BY sort_column) AS prev_value,
    LEAD(column_name, offset, default) OVER (ORDER BY sort_column) AS next_value
FROM table_name;

offset is how many rows back or forward to look. It defaults to 1. default is what to return when there's no row at that position. It defaults to NULL.

Here's a direct example. This returns each order alongside the previous order's total_amount, sorted by order_date:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS prev_order_amount
FROM orders o;

The first row has no preceding row, so prev_order_amount returns 0 because that's the default we passed in. Every other row shows the total_amount from the row before it in date order.

Computing Row-to-Row Differences

The most common use of LAG is subtracting the previous value from the current one. You can do that arithmetic directly in SELECT.

This query shows how much each order's value changed compared to the previous order:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS prev_amount,
    o.total_amount - LAG(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS change_from_prev
FROM orders o;

A positive change_from_prev means the order was higher than the last one. A negative value means it was lower. No join required.

LEAD works the same way but in the opposite direction. This returns each order alongside the next order's amount:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    LEAD(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS next_order_amount
FROM orders o;

The last row has no following row, so next_order_amount returns 0.

Using PARTITION BY to Reset Per Group

Without PARTITION BY, LAG and LEAD look across the entire result. The previous row for the first order in a new group would be the last order from the previous group. That's usually not what you want.

Add PARTITION BY to keep comparisons within each group:

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    LAG(e.salary, 1, 0) OVER (PARTITION BY e.department ORDER BY e.hire_date) AS prev_hire_salary
FROM employees e;

Now prev_hire_salary shows the salary of the employee hired just before in the same department. The first hire in each department gets 0 because there's no preceding row within that partition.

Using an Offset Greater Than 1

You're not limited to one row back or forward. Pass a different number as the second argument to jump further.

This pulls the salary from two hires back in the same department:

SELECT
    e.employee_id,
    e.department,
    e.hire_date,
    e.salary,
    LAG(e.salary, 2, 0) OVER (PARTITION BY e.department ORDER BY e.hire_date) AS two_hires_ago_salary
FROM employees e;

The first two rows in each partition return 0 because there aren't two preceding rows within that group.

How LAG and LEAD Connect to Other Concepts

LAG and LEAD use the same OVER clause structure as [ranking functions like RANK and ROW_NUMBER](/learn/sql-rank-window-function). If you're comfortable with those, the syntax here transfers directly. The difference is what the function returns: a rank number versus a value from another row.

If you need to filter on the result of LAG or LEAD, wrap the query in a [subquery or CTE](/learn/sql-cte-common-table-expressions). You can't reference a window function alias in WHERE at the same query level.

Common Mistakes

1. Forgetting that the first or last row returns NULL by default

Without a default value, LAG on the first row and LEAD on the last row return NULL, which can cause unexpected results in calculations.

-- wrong: NULL on first row breaks subtraction
SELECT
    o.order_id,
    o.total_amount,
    o.total_amount - LAG(o.total_amount) OVER (ORDER BY o.order_date) AS change
FROM orders o;

Pass a default as the third argument to control what appears when there's no adjacent row.

-- correct: 0 used as default for first row
SELECT
    o.order_id,
    o.total_amount,
    o.total_amount - LAG(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS change
FROM orders o;

2. Omitting PARTITION BY when comparing within groups

Without PARTITION BY, the previous row for the first record in a new group is the last record from the previous group, which produces incorrect cross-group comparisons.

-- wrong: LAG crosses department boundaries
SELECT
    e.employee_id,
    e.department,
    e.salary,
    LAG(e.salary, 1, 0) OVER (ORDER BY e.hire_date) AS prev_salary
FROM employees e;

Add PARTITION BY to keep comparisons within each department.

-- correct
SELECT
    e.employee_id,
    e.department,
    e.salary,
    LAG(e.salary, 1, 0) OVER (PARTITION BY e.department ORDER BY e.hire_date) AS prev_salary
FROM employees e;

3. Filtering on a LAG alias in WHERE

Window functions run after WHERE, so the alias doesn't exist when SQL evaluates the filter.

-- wrong
SELECT
    o.order_id,
    o.total_amount,
    LAG(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS prev_amount
FROM orders o
WHERE prev_amount > 500;

Wrap the query in a subquery so the column is available to filter on.

-- correct
SELECT * FROM (
    SELECT
        o.order_id,
        o.total_amount,
        LAG(o.total_amount, 1, 0) OVER (ORDER BY o.order_date) AS prev_amount
    FROM orders o
) t
WHERE prev_amount > 500;

Quick Recap

• LAG(column, offset, default) returns a value from a preceding row. LEAD(column, offset, default) returns a value from a following row.
• Both use ORDER BY inside OVER to define which row is "previous" or "next."
• The default offset is 1. Increase it to look further back or forward in the sequence.
• Without a default value, LAG on the first row and LEAD on the last row return NULL.
• Add PARTITION BY to reset the comparison within each group rather than across the entire result.
• To filter on a LAG or LEAD result, wrap the query in a subquery or [CTE](/learn/sql-cte-common-table-expressions) and apply WHERE in the outer query.

SELECT
    e.employee_id,
    e.hire_date,
    e.salary,
    LAG(e.salary, 1, 0) OVER (ORDER BY e.hire_date) AS prev_salary
FROM employees e;

SELECT
    e.employee_id,
    e.hire_date,
    e.salary,
    LEAD(e.salary, 1, 0) OVER (ORDER BY e.hire_date) AS next_salary
FROM employees e;

SELECT
    pay.payment_id,
    pay.payment_date,
    pay.amount,
    LAG(pay.amount, 1, 0) OVER (ORDER BY pay.payment_date) AS prev_amount,
    pay.amount - LAG(pay.amount, 1, 0) OVER (ORDER BY pay.payment_date) AS change_from_prev
FROM payments pay;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    LAG(pay.amount, 1, 0) OVER (PARTITION BY pay.method ORDER BY pay.payment_date) AS prev_method_amount
FROM payments pay
WHERE pay.status = 'completed';

SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.payment_date,
        pay.amount,
        LAG(pay.amount, 1, 0) OVER (ORDER BY pay.payment_date) AS prev_amount
    FROM payments pay
) t
WHERE amount > prev_amount;