Window functions Updated November 2026

LAG and LEAD

LAG(col) OVER (ORDER BY date) returns the value of col from the previous row and LEAD(col) from the next row, as a new column on the current row. Use them for differences between consecutive rows, such as month-over-month change. An optional offset and default control how far to look and the edge value.

Syntax

LAG(column [, offset [, default]]) OVER ([PARTITION BY cols] ORDER BY cols)

Part	What it is
`column`	The value to fetch from the other row
`offset`	How many rows back (LAG) or forward (LEAD); default 1
`default`	Value to return when there is no such row; default NULL

Examples

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

Result: month | revenue | prev | change 2025-01 | 12500 | NULL | NULL 2025-02 | 9800 | 12500 | -2700 2025-03 | 11000 | 9800 | 1200

SELECT customer_id, order_date,
       julianday(order_date) - julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since_last
FROM orders;

Result: Days between each customer's consecutive orders (SQLite)

In PostgreSQL subtract the dates directly: order_date - LAG(order_date) OVER (...).

SELECT id, amount, LEAD(amount, 1, 0) OVER (ORDER BY id) AS next_amount FROM orders;

Result: The next order's amount on each row, 0 on the last row

Common mistakes

Omitting ORDER BY inside OVER makes previous and next meaningless; LAG needs an order.
Forgetting PARTITION BY when comparing within a customer or product, so the first row of each group picks up the last row of the previous group.
Expecting LAG to skip gaps in dates; it returns the previous row, whatever its date, so fill missing periods first if you need true month-to-month comparisons.

Practice LAG and LEAD

These drills check your answer on live data, free, no account needed.

Change from the previous orderFor every paid order, return the id and the difference between its amount and the amount of the previous paid order, ordered by order date then id. The first order shows NULL.
Month over monthReturn each month (like 2025-01), its paid revenue, and the change from the previous month's paid revenue. The first month shows NULL for the change.

Questions

How do I calculate the difference from the previous row in SQL?

value - LAG(value) OVER (ORDER BY ordering_column). Wrap the query in a CTE if you want to filter or sort on the difference.

How do I compute percent change month over month?

100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month). Use NULLIF(LAG(...), 0) in the denominator to avoid division by zero.

Window functionsWindow functions compute rankings, running totals, shares and previous-row values without collapsing rows. The OVER clause, PARTITION BY, ORDER BY and frames.
PARTITION BYPARTITION BY inside OVER splits rows into groups so a window function restarts per group. Rank per department, share of group total, PARTITION BY vs GROUP BY.
Running totalA running total adds each row's value to the sum of all previous rows. SUM() OVER (ORDER BY), per-group totals with PARTITION BY, ROWS frames, moving average.
Date functionsGet today's date, extract the year or month, group by month, add days and compute date differences in SQLite (strftime, julianday) and PostgreSQL (EXTRACT).