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)

-- Calculate the previous day's sales and the difference.
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;

-- Compare previous and next month's sales side by side, grouped by product category.
SELECT
    sale_month,
    category_name,
    sales_amount,
    LAG(sales_amount)  OVER (PARTITION BY category_name ORDER BY sale_month) AS prev_month_sales,
    LEAD(sales_amount) OVER (PARTITION BY category_name ORDER BY sale_month) AS next_month_sales
FROM monthly_category_sales
ORDER BY category_name, sale_month;

-- Day-over-day comparison.
sale_date   | sales_amount | prev_day_sales | 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

-- Month-over-month comparison by category.
sale_month | category_name | sales_amount | prev_month_sales | next_month_sales
-----------+---------------+--------------+------------------+-----------------
2024-01    | Electronics   | 850000       | NULL             | 920000
2024-02    | Electronics   | 920000       | 850000           | 780000
2024-03    | Electronics   | 780000       | 920000           | NULL

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

-- Calculate the previous day's sales and the difference (works across major databases).
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).