SQL Window Functions Explained: OVER, LAG, LEAD
· 7 min read
SQL window functions calculate a value across a set of related rows without collapsing them. GROUP BY turns many rows into one per group. A window function keeps every row and adds a new column, such as the group's total, the previous month's value, or a rank. You turn any aggregate into a window function by adding OVER (...).
SELECT region, month, revenue,
SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM monthly_sales
ORDER BY region, month;Every month keeps its own row, and each one also shows its region's total. Below: that output, what goes inside OVER, LAG and LEAD for month-over-month change, moving averages, and the missing PARTITION BY that leaks numbers between groups.
A worked example
| region | month | revenue |
|---|---|---|
| North | 2025-01 | 1200 |
| North | 2025-02 | 1500 |
| North | 2025-03 | 1350 |
| South | 2025-01 | 900 |
| South | 2025-02 | 800 |
| South | 2025-03 | 1100 |
Run the query from the top on it:
SELECT region, month, revenue,
SUM(revenue) OVER (PARTITION BY region) AS region_total
FROM monthly_sales
ORDER BY region, month;| region | month | revenue | region_total |
|---|---|---|---|
| North | 2025-01 | 1200 | 4050 |
| North | 2025-02 | 1500 | 4050 |
| North | 2025-03 | 1350 | 4050 |
| South | 2025-01 | 900 | 2800 |
| South | 2025-02 | 800 | 2800 |
| South | 2025-03 | 1100 | 2800 |
Compare it with the GROUP BY version, SELECT region, SUM(revenue) FROM monthly_sales GROUP BY region, which returns just two rows: North 4050 and South 2800. Same totals, but the monthly detail is gone. With a window function you get both, so you can compare each row with its group in the same query.
Window functions explained: what goes inside OVER
OVER defines the "window": which rows the function looks at for the current row. It has three optional parts:
| Part | What it does | Example |
|---|---|---|
PARTITION BY | splits rows into groups; the function restarts in each | PARTITION BY region |
ORDER BY | sets the order inside each group | ORDER BY month |
frame (ROWS BETWEEN ...) | limits the window to nearby rows | ROWS BETWEEN 1 PRECEDING AND CURRENT ROW |
An empty OVER () means "all rows". SUM(revenue) OVER () puts the grand total, 6850, on every row.
OVER PARTITION BY: share of a group
The most practical use of OVER (PARTITION BY ...) is a percentage of a group total:
SELECT region, month, revenue,
ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 1) AS pct_of_region
FROM monthly_sales
ORDER BY region, month;| region | month | revenue | pct_of_region |
|---|---|---|---|
| North | 2025-01 | 1200 | 29.6 |
| North | 2025-02 | 1500 | 37.0 |
| North | 2025-03 | 1350 | 33.3 |
| South | 2025-01 | 900 | 32.1 |
| South | 2025-02 | 800 | 28.6 |
| South | 2025-03 | 1100 | 39.3 |
Each region's percentages add up to about 100. Without a window function you would need a subquery or a join back to a totals table. The 100.0 matters: in Postgres and SQL Server, integer divided by integer drops the decimals.
Add ORDER BY inside OVER and SUM becomes a cumulative sum. That is a topic of its own, covered in SQL running total. Ranking functions (RANK, DENSE_RANK, ROW_NUMBER) follow the same pattern and are covered in RANK OVER PARTITION BY.
LAG and LEAD: compare with the previous or next row
LAG(column) returns the value from the row before the current one, in the order you specify. LEAD(column) returns the value from the row after. They are the standard way to calculate month-over-month change:
SELECT region, month, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue,
revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS change
FROM monthly_sales
ORDER BY region, month;| region | month | revenue | prev_revenue | change |
|---|---|---|---|---|
| North | 2025-01 | 1200 | NULL | NULL |
| North | 2025-02 | 1500 | 1200 | 300 |
| North | 2025-03 | 1350 | 1500 | -150 |
| South | 2025-01 | 900 | NULL | NULL |
| South | 2025-02 | 800 | 900 | -100 |
| South | 2025-03 | 1100 | 800 | 300 |
January has no earlier month, so LAG returns NULL. That is correct: there is nothing to compare with. If you need a default, LAG(revenue, 1, 0) returns 0 instead. The middle argument is how many rows back to look, so LAG(revenue, 12) on monthly data gives the same month last year.
LEAD works the same way, looking forward:
SELECT region, month, revenue,
LEAD(revenue) OVER (PARTITION BY region ORDER BY month) AS next_revenue
FROM monthly_sales
ORDER BY region, month;| region | month | revenue | next_revenue |
|---|---|---|---|
| North | 2025-01 | 1200 | 1500 |
| North | 2025-02 | 1500 | 1350 |
| North | 2025-03 | 1350 | NULL |
| South | 2025-01 | 900 | 800 |
| South | 2025-02 | 800 | 1100 |
| South | 2025-03 | 1100 | NULL |
Moving averages with a frame
A frame narrows the window to nearby rows. This is a two-month moving average:
SELECT region, month, revenue,
AVG(revenue) OVER (
PARTITION BY region ORDER BY month
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS avg_2m
FROM monthly_sales
ORDER BY region, month;| region | month | revenue | avg_2m |
|---|---|---|---|
| North | 2025-01 | 1200 | 1200 |
| North | 2025-02 | 1500 | 1350 |
| North | 2025-03 | 1350 | 1425 |
| South | 2025-01 | 900 | 900 |
| South | 2025-02 | 800 | 850 |
| South | 2025-03 | 1100 | 950 |
The first month only has itself to average. Use 2 PRECEDING for a three-month average.
Common mistakes
Leaving out PARTITION BY (the AI mistake to watch for)
Ask an AI assistant for "each region's change from the previous month" and you may get this:
SELECT region, month, revenue,
LAG(revenue) OVER (ORDER BY region, month) AS prev_revenue
FROM monthly_sales
ORDER BY region, month;| region | month | revenue | prev_revenue |
|---|---|---|---|
| North | 2025-01 | 1200 | NULL |
| North | 2025-02 | 1500 | 1200 |
| North | 2025-03 | 1350 | 1500 |
| South | 2025-01 | 900 | 1350 |
| South | 2025-02 | 800 | 900 |
| South | 2025-03 | 1100 | 800 |
Look at South in January: its "previous month" is 1350, which is North's March. The window treats all rows as one long list, so the last North row leaks into the first South row. Five rows out of six are right, which is exactly why it gets past a quick glance. The assistant would report that South fell by 450 in January.
How to spot it: the first row of every group should have NULL from LAG. If only the very first row of the whole result does, the PARTITION BY is missing. Ordering by the group column (ORDER BY region, month) is not the same as partitioning by it.
Filtering on a window function in WHERE
WHERE LAG(revenue) OVER (...) < revenue fails. DuckDB says WHERE clause cannot contain window functions!. Window functions are calculated after WHERE, so compute them in a CTE and filter outside:
WITH with_prev AS (
SELECT region, month, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev_revenue
FROM monthly_sales
)
SELECT region, month, revenue, prev_revenue
FROM with_prev
WHERE revenue > prev_revenue
ORDER BY region, month;| region | month | revenue | prev_revenue |
|---|---|---|---|
| North | 2025-02 | 1500 | 1200 |
| South | 2025-03 | 1100 | 800 |
Why the order matters is explained in SQL order of execution.
Forgetting ORDER BY inside OVER
LAG and LEAD without an ORDER BY in OVER have no defined "previous row". Some databases reject it; others return whatever order they read the rows in. Always order by the date.
FAQ
What is the difference between GROUP BY and a window function?
GROUP BY returns one row per group. A window function returns every input row and adds a calculated column. Use GROUP BY for a summary table and a window function when you need detail and group values side by side.
What does OVER (PARTITION BY) do in SQL?
It tells the window function to restart for each value of the partition column. SUM(revenue) OVER (PARTITION BY region) gives each row its own region's total.
What is the difference between LAG and LEAD?
LAG looks back to an earlier row and LEAD looks ahead to a later row, both in the order set by ORDER BY inside OVER.
Do window functions work in MySQL?
Yes, from MySQL 8.0. They also work in DuckDB, Postgres, SQL Server, Oracle and SQLite (3.25 and later).
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Revenue so far this year” →