
Sooner or later every developer writes a query like this one:
"Show every order, and the customer's total spend next to it."
"Give me the top 3 products in each category."
"Add a running total column."
"Compare each month's revenue with the previous month."
The first instinct is usually a self-join or a correlated subquery. It works, it's slow, and six months later nobody wants to read it.
Window functions are the clean answer to all four. Once they click, you'll reach for them weekly.
All examples use PostgreSQL syntax, but window functions are standard SQL. They work in PostgreSQL 8.4+, MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+, SQL Server 2012+, Oracle, BigQuery, and Snowflake. Check your version if something errors out.
The one idea: keep the rows, add a calculation
GROUP BY squashes many rows into one. A window function does the same kind of math, but keeps every row and attaches the result to it.
Here's the dataset we'll use for the whole post. Paste it into psql or any SQL playground:
CREATE TABLE sales (
id INT PRIMARY KEY,
region TEXT,
rep TEXT,
sale_date DATE,
amount INT
);
INSERT INTO sales VALUES
(1, 'North', 'Asha', '2026-01-05', 500),
(2, 'North', 'Ravi', '2026-01-12', 300),
(3, 'North', 'Asha', '2026-02-03', 700),
(4, 'North', 'Meera', '2026-02-20', 500),
(5, 'South', 'Kabir', '2026-01-08', 400),
(6, 'South', 'Neha', '2026-01-25', 600),
(7, 'South', 'Kabir', '2026-02-14', 500),
(8, 'South', 'Neha', '2026-03-02', 200);With GROUP BY you get one row per region:
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;region | region_total |
|---|---|
North | 2000 |
South | 1700 |
With a window function you get all 8 rows, each carrying its region's total:
SELECT id, region, rep, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY id;id | region | rep | amount | region_total |
|---|---|---|---|---|
1 | North | Asha | 500 | 2000 |
2 | North | Ravi | 300 | 2000 |
3 | North | Asha | 700 | 2000 |
4 | North | Meera | 500 | 2000 |
5 | South | Kabir | 400 | 1700 |
6 | South | Neha | 600 | 1700 |
7 | South | Kabir | 500 | 1700 |
8 | South | Neha | 200 | 1700 |
That OVER (...) clause is what turns an ordinary function into a window function.
Anatomy of an OVER clause
function_name(arguments) OVER (
PARTITION BY column_a, column_b -- optional: split rows into groups
ORDER BY column_c -- optional: order rows inside each group
ROWS BETWEEN ... AND ... -- optional: which rows the function can see
)Each part has one job:
PARTITION BYsplits the rows into independent groups (the "windows"). Leave it out and the whole result set is one group.ORDER BYsorts the rows inside each group. Ranking and "previous row" logic need it.The frame (
ROWS BETWEEN ...) narrows down which rows, relative to the current one, the function actually looks at.
For every row, the database does this:
flowchart LR
A["Rows left after<br/>FROM, WHERE, GROUP BY"] --> B["PARTITION BY<br/>split into groups"]
B --> C["ORDER BY<br/>sort inside each group"]
C --> D["Frame<br/>rows visible to the current row"]
D --> E["Function runs<br/>SUM, RANK, LAG ..."]
E --> F["Result attached to the row<br/>no rows are removed"]Aggregates as window functions
Any aggregate (SUM, AVG, COUNT, MIN, MAX) can become a window function just by adding OVER. That makes "share of the group" and "share of the total" queries trivial:
SELECT id, region, amount,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region,
ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_total
FROM sales
ORDER BY id;id | region | amount | pct_of_region | pct_of_total |
|---|---|---|---|---|
1 | North | 500 | 25.0 | 13.5 |
2 | North | 300 | 15.0 | 8.1 |
3 | North | 700 | 35.0 | 18.9 |
4 | North | 500 | 25.0 | 13.5 |
5 | South | 400 | 23.5 | 10.8 |
6 | South | 600 | 35.3 | 16.2 |
7 | South | 500 | 29.4 | 13.5 |
8 | South | 200 | 11.8 | 5.4 |
The empty OVER () is the "no PARTITION BY" case from above: the whole result is one window, so pct_of_total divides by the grand total (3700).
Ranking: ROW_NUMBER, RANK, DENSE_RANK
These three look alike but handle ties differently. North has two sales of 500, which makes the difference easy to see:
SELECT id, region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS row_num,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rnk
FROM sales
ORDER BY region, amount DESC, id;id | region | rep | amount | row_num | rnk | dense_rnk |
|---|---|---|---|---|---|---|
3 | North | Asha | 700 | 1 | 1 | 1 |
1 | North | Asha | 500 | 2 | 2 | 2 |
4 | North | Meera | 500 | 3 | 2 | 2 |
2 | North | Ravi | 300 | 4 | 4 | 3 |
6 | South | Neha | 600 | 1 | 1 | 1 |
7 | South | Kabir | 500 | 2 | 2 | 2 |
5 | South | Kabir | 400 | 3 | 3 | 3 |
8 | South | Neha | 200 | 4 | 4 | 4 |
ROW_NUMBER()numbers every row uniquely, even on ties. Which tied row comes first is arbitrary unless you add a tiebreaker, which is whyidis in theORDER BYabove. Without one, the numbering can change from run to run.RANK()gives ties the same rank, then skips ahead: two rows at rank 2, so the next is rank 4.DENSE_RANK()gives ties the same rank and never skips: after two rank-2 rows comes rank 3.
Need buckets instead of ranks? NTILE(4) deals the rows into four roughly equal buckets, which is handy for quartiles.
Top-N per group
This is the most common use case. You can't filter on a window function in the same query's WHERE (see Pitfalls), so compute the rank in a CTE and filter outside:
WITH ranked AS (
SELECT id, region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS rn
FROM sales
)
SELECT id, region, rep, amount
FROM ranked
WHERE rn <= 2;id | region | rep | amount |
|---|---|---|---|
3 | North | Asha | 700 |
1 | North | Asha | 500 |
6 | South | Neha | 600 |
7 | South | Kabir | 500 |
Use ROW_NUMBER() when you want exactly N rows per group. Use RANK() or DENSE_RANK() when ties should all be included.
Looking at neighbouring rows: LAG and LEAD
LAG(col, n) looks n rows back within the partition and LEAD(col, n) looks n rows ahead. n defaults to 1. If there's no such row you get NULL, or a default you pass as the third argument.
Month-over-month change is the textbook example. Aggregate first, then look backwards:
WITH monthly AS (
SELECT region,
DATE_TRUNC('month', sale_date)::date AS month,
SUM(amount) AS revenue
FROM sales
GROUP BY region, DATE_TRUNC('month', sale_date)
)
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
ORDER BY region, month;region | month | revenue | prev_revenue | change |
|---|---|---|---|---|
North | 2026-01-01 | 800 | NULL | NULL |
North | 2026-02-01 | 1200 | 800 | 400 |
South | 2026-01-01 | 1000 | NULL | NULL |
South | 2026-02-01 | 500 | 1000 | -500 |
South | 2026-03-01 | 200 | 500 | -300 |
Notice the PARTITION BY region. Without it, the rows of both regions are sorted into one list by month, so each row gets compared with whichever region's row happens to sit just before it. South's January would show North's 800 as its "previous" month. There's no error, just wrong numbers.
Tip: Because window functions run after
GROUP BY(more in Pitfalls), they can take aggregates as arguments.LAG(SUM(amount)) OVER (...)works directly in a grouped query, no CTE required. The CTE version is just easier to read.
On SQLite, replace both occurrences of DATE_TRUNC('month', sale_date) (and the ::date cast) with strftime('%Y-%m-01', sale_date).
Running totals and moving averages
Add an ORDER BY inside OVER to an aggregate and it becomes cumulative. Add an explicit frame and you control the window size:
SELECT id, region, sale_date, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total,
ROUND(AVG(amount) OVER (
PARTITION BY region ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 1) AS moving_avg_3
FROM sales
ORDER BY region, sale_date;id | region | sale_date | amount | running_total | moving_avg_3 |
|---|---|---|---|---|---|
1 | North | 2026-01-05 | 500 | 500 | 500.0 |
2 | North | 2026-01-12 | 300 | 800 | 400.0 |
3 | North | 2026-02-03 | 700 | 1500 | 500.0 |
4 | North | 2026-02-20 | 500 | 2000 | 500.0 |
5 | South | 2026-01-08 | 400 | 400 | 400.0 |
6 | South | 2026-01-25 | 600 | 1000 | 500.0 |
7 | South | 2026-02-14 | 500 | 1500 | 500.0 |
8 | South | 2026-03-02 | 200 | 1700 | 433.3 |
The 3-row moving average for the last South row is (600 + 500 + 200) / 3 = 433.3. At the start of each partition there aren't 3 rows yet, so the average uses however many exist.
Understanding frames
Frame bounds are built from these pieces:
Bound | Meaning |
|---|---|
| from the first row of the partition |
| n rows before the current row |
| the current row |
| n rows after the current row |
| to the last row of the partition |
Two defaults are worth memorising:
No
ORDER BYin theOVERclause: the frame is the whole partition.With
ORDER BYand no explicit frame: the frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That's what makes a running total work, but it also causes the most common window-function bug.
The LAST_VALUE gotcha
Because of that default frame, LAST_VALUE doesn't return the last row of the partition. It returns the last row up to the current one, which is usually the current row itself:
SELECT id, rep, sale_date,
FIRST_VALUE(rep) OVER (PARTITION BY region ORDER BY sale_date) AS first_rep,
LAST_VALUE(rep) OVER (PARTITION BY region ORDER BY sale_date) AS last_rep_wrong,
LAST_VALUE(rep) OVER (
PARTITION BY region ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_rep
FROM sales
WHERE region = 'North'
ORDER BY sale_date;id | rep | sale_date | first_rep | last_rep_wrong | last_rep |
|---|---|---|---|---|---|
1 | Asha | 2026-01-05 | Asha | Asha | Meera |
2 | Ravi | 2026-01-12 | Asha | Ravi | Meera |
3 | Asha | 2026-02-03 | Asha | Asha | Meera |
4 | Meera | 2026-02-20 | Asha | Meera | Meera |
FIRST_VALUE is fine with the default frame. For LAST_VALUE (and NTH_VALUE), spell the frame out.
ROWS vs RANGE
ROWScounts physical rows. "2 PRECEDING" means exactly two rows back.RANGEworks on values of theORDER BYcolumn, and includes every peer row that has the same value.
If two sales share a date, SUM(amount) OVER (ORDER BY sale_date) gives both rows the same running total, because they are peers. When that surprises you, either add a unique tiebreaker to the ORDER BY (like id) or switch to ROWS.
Two real-world recipes
1. Deduplicate: keep the latest row per key
Duplicated signups, repeated webhook deliveries, multiple versions of a record. This is the top-N pattern from earlier with N = 1: number the rows per key, newest first, and keep number 1.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, id DESC
) AS rn
FROM signups
)
SELECT * FROM ranked WHERE rn = 1;To delete the older duplicates instead, keep the same CTE and change the last statement:
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, id DESC) AS rn
FROM signups
)
DELETE FROM signups
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);Run the SELECT version first and check the row count before you delete anything.
2. Gaps and islands: find consecutive streaks
"How many days in a row did each user log in?" Subtract a row number from the date. Consecutive dates produce the same result, and every gap changes it:
-- logins(user_id, login_date): login_date is a DATE, one row per user per day
-- (if a user can log in several times a day, use SELECT DISTINCT first)
WITH numbered AS (
SELECT user_id,
login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date
))::int AS grp
FROM logins
)
SELECT user_id,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
COUNT(*) AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY user_id, streak_start;Take one user who logged in on Jan 1, 2, 3, 5 and 6:
login_date | row_number | login_date − row_number |
|---|---|---|
2026-01-01 | 1 | 2025-12-31 |
2026-01-02 | 2 | 2025-12-31 |
2026-01-03 | 3 | 2025-12-31 |
2026-01-05 | 4 | 2026-01-01 |
2026-01-06 | 5 | 2026-01-01 |
Two distinct values means two islands: Jan 1 to 3 (3 days) and Jan 5 to 6 (2 days). The same trick finds runs of consecutive IDs, uninterrupted uptime, or winning streaks.
Named windows: stop repeating yourself
When several columns share the same window, give it a name with the WINDOW clause:
SELECT id, region, amount,
SUM(amount) OVER w AS running_total,
AVG(amount) OVER w AS running_avg,
COUNT(*) OVER w AS sales_so_far
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date)
ORDER BY region, sale_date;It's shorter, and it can't drift out of sync when you edit one copy and forget the others. (Supported in PostgreSQL, MySQL 8+, recent SQLite versions, and SQL Server 2022+.)
Pitfalls to avoid
You can't use a window function in WHERE
The database evaluates WHERE long before window functions run, so this fails:
-- ERROR: window functions are not allowed in WHERE
SELECT * FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) <= 2;The logical order a query runs in looks like this:
flowchart LR
A["FROM / JOIN"] --> B["WHERE"]
B --> C["GROUP BY / HAVING"]
C --> D["Window functions"]
D --> E["SELECT / DISTINCT"]
E --> F["ORDER BY / LIMIT"]Window functions only see rows that survived WHERE, GROUP BY and HAVING. To filter on their results, wrap the query in a CTE or subquery, like the top-N example above. Snowflake, BigQuery, and DuckDB also offer a QUALIFY clause that does this in one step.
NULL ordering differs by database
PostgreSQL treats NULL as larger than any value, so ORDER BY x DESC puts NULLs first. MySQL and SQLite treat NULL as smaller. Add NULLS FIRST or NULLS LAST (where supported) when ranking nullable columns.
Performance
Window functions need sorted data. A few habits keep them fast:
Index the
PARTITION BYandORDER BYcolumns together, for example(region, sale_date).Reuse the same
OVERdefinition where you can. PostgreSQL can compute several functions that share a window in one pass over one sort, while a window with a differentPARTITION BYorORDER BYmay need a sort of its own.Check the plan with
EXPLAIN ANALYZE. In PostgreSQL, look for theWindowAggnode and anySortfeeding it.Filter rows in
WHEREfirst. Fewer rows entering the window means less sorting.
Cheat sheet
Function | What it does |
|---|---|
| Unique sequential number per row |
| Rank with gaps after ties |
| Rank without gaps |
| Splits the partition into n buckets |
| Value from n rows before |
| Value from n rows after |
| First / last value in the frame |
| The nth value in the frame |
| Aggregate over the frame |
| Relative position of a row within its partition |
Everything except the aggregates only makes sense with an ORDER BY inside OVER.
Wrapping up
Window functions boil down to one sentence: do a calculation over a related set of rows, without losing the rows you started with.
A good way to practise: take one of your own tables and try three tasks. Rank rows within a group, compute a running total, and compare each row to the previous one with LAG. After that, the rest of this post is just variations.
Comments(0)
Join the discussion by logging into your account.
No comments yet. Be the first to comment!