LAG / LEAD

LAG: References the value N rows before the current row.

LAG(column [, N [, default]]) OVER ([PARTITION BY group_column] ORDER BY sort_column)

LEAD: References the value N rows after the current row.

LEAD(column [, N [, default]]) OVER ([PARTITION BY group_column] ORDER BY sort_column)

Function	Description
LAG(column, N, default)	Returns the value N rows before the current row. If N is omitted, defaults to 1. Returns the default value (NULL if omitted) when no preceding row exists.
LEAD(column, N, default)	Returns the value N rows after the current row. If N is omitted, defaults to 1. Returns the default value (NULL if omitted) when no following row exists.

The following examples use the daily_sales table.

Calculate the previous day's sales and the difference.

sample_lag_lead.sql

SELECT
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_day_amount,
    amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS day_over_day
FROM daily_sales
ORDER BY sale_date;

+------------+--------+-----------------+--------------+
| sale_date  | amount | prev_day_amount | day_over_day |
+------------+--------+-----------------+--------------+
| 2024-01-01 | 120000 |               0 |       120000 |
| 2024-01-02 |  98000 |          120000 |       -22000 |
| 2024-01-03 | 145000 |           98000 |        47000 |
| 2024-01-04 |  88000 |          145000 |       -57000 |
+------------+--------+-----------------+--------------+
4 rows in set

Compare previous and next month's sales side by side, grouped by product category.

sample_lag_lead.sql

SELECT
    sale_month,
    category,
    amount,
    LAG(amount)  OVER (PARTITION BY category ORDER BY sale_month) AS prev_month,
    LEAD(amount) OVER (PARTITION BY category ORDER BY sale_month) AS next_month
FROM monthly_category_sales
ORDER BY category, sale_month;

+------------+-------------+--------+------------+------------+
| sale_month | category    | amount | prev_month | next_month |
+------------+-------------+--------+------------+------------+
|    2024-01 | Electronics | 850000 | NULL       |     920000 |
|    2024-02 | Electronics | 920000 |     850000 |     780000 |
|    2024-03 | Electronics | 780000 |     920000 | NULL       |
+------------+-------------+--------+------------+------------+
3 rows in set

LAG() and LEAD() are supported in MySQL (8.0+), PostgreSQL, Oracle, SQL Server, and SQLite (3.25+).

SELECT
    sale_date,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS prev_day_sales,
    sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sale_date) AS day_over_day
FROM daily_sales
ORDER BY sale_date;

LAG and LEAD are window functions that return values from N rows before or after the current row. The second argument sets the offset (defaults to 1 if omitted), and the third argument sets the default value returned when no such row exists. If the default is omitted, NULL is returned.

Day-over-day and month-over-month calculations can also be done with a self join, but LAG/LEAD produces much more concise code. When you add PARTITION BY, the offset is applied independently within each group — always specify it when multiple series share a single table. Without it, offsets may cross group boundaries.

For the basic window function syntax, see OVER / PARTITION BY. For running totals and moving averages, see SUM / AVG (Window).