SQL Window Functions: ROW_NUMBER, Running Totals, Frames and Gaps-and-Islands
The classic way to get "the best day per region" is a GROUP BY subquery joined back to the table, which breaks as soon as two days tie. Window functions fix that class of problem. They compute across a set of related rows like an aggregate does, but every input row stays in the output, so you can rank, number, total and compare without collapsing anything.
All the examples below run in SQLite and PostgreSQL. SQLite added window functions in 3.25.0 and window chaining, which one example uses, in 3.28.0. I ran them in SQLite 3.51 and pasted the real output. One date expression differs in Postgres, and I show both versions.
The setup
CREATE TABLE sales (sold_on TEXT NOT NULL, region TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES
('2026-11-01', 'eu', 100), ('2026-11-01', 'us', 80),
('2026-11-02', 'eu', 100), ('2026-11-02', 'us', 120),
('2026-11-03', 'eu', 60), ('2026-11-03', 'us', 120),
('2026-11-04', 'eu', 90), ('2026-11-04', 'us', 40);
The ties are deliberate. Most window-function surprises only show up when two rows share an ORDER BY value.
Anatomy of OVER
function(...) OVER (
PARTITION BY ... -- split rows into independent groups (optional)
ORDER BY ... -- order inside each group (optional, but most functions need it)
frame clause -- which rows around the current one to include (optional)
)
Window functions are evaluated after WHERE, GROUP BY and HAVING, which is why the Postgres tutorial says they're only allowed in the SELECT list and ORDER BY. You can't filter on one in WHERE; wrap the query in a subquery or CTE instead.
Numbering and ranking
SELECT region, sold_on, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, sold_on) 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
FROM sales
ORDER BY region, row_num;
region sold_on amount row_num rnk dense
------ ---------- ------ ------- --- -----
eu 2026-11-01 100 1 1 1
eu 2026-11-02 100 2 1 1
eu 2026-11-04 90 3 3 2
eu 2026-11-03 60 4 4 3
us 2026-11-02 120 1 1 1
us 2026-11-03 120 2 1 1
us 2026-11-01 80 3 3 2
us 2026-11-04 40 4 4 3
The three differ only on ties. ROW_NUMBER always gives 1, 2, 3, 4. RANK gives tied rows the same number and then leaves a gap; DENSE_RANK doesn't leave a gap because it "effectively counts peer groups."
Notice the sold_on tiebreaker in the ROW_NUMBER window. Without it, which of two 100-sales days gets row 1 is up to the engine and can change between runs or versions. If your pagination or deduplication uses ROW_NUMBER, make the ORDER BY unique.
Top N per group
This is the job most people first reach for window functions to do. Filter in an outer query, exactly as the Postgres tutorial shows:
SELECT region, sold_on, amount
FROM (
SELECT region, sold_on, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, sold_on) AS rn
FROM sales
) AS ranked
WHERE rn = 1;
region sold_on amount
------ ---------- ------
eu 2026-11-01 100
us 2026-11-02 120
Want every tied winner instead of one? Swap ROW_NUMBER for RANK and keep rn = 1. It's the same "most recent order per customer" pattern that shows up whenever you'd otherwise reach for a correlated subquery.
Running totals, and the frame default that surprises everyone
SELECT sold_on, region, amount,
SUM(amount) OVER (ORDER BY sold_on) AS range_total,
SUM(amount) OVER (ORDER BY sold_on, region ROWS UNBOUNDED PRECEDING) AS rows_total
FROM sales
ORDER BY sold_on, region;
sold_on region amount range_total rows_total
---------- ------ ------ ----------- ----------
2026-11-01 eu 100 180 100
2026-11-01 us 80 180 180
2026-11-02 eu 100 400 280
2026-11-02 us 120 400 400
2026-11-03 eu 60 580 460
2026-11-03 us 120 580 580
2026-11-04 eu 90 710 670
2026-11-04 us 40 710 710
Two rows on the same date get the same range_total. That's not a bug. With an ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which runs through the current row's last ORDER BY peer, not the current row itself. Rows with equal sort values are peers, so they're added together. MySQL documents the same default.
If you want a row-by-row running balance, say ROWS explicitly and give it a unique order. If you want "total as of end of this day," the default is exactly right. Pick on purpose.
Moving averages and comparisons with LAG
Frames earn their keep in rolling calculations. Here's a per-region running total, a two-day moving average and the day-over-day change, sharing one named window:
SELECT region, sold_on, amount,
SUM(amount) OVER w AS running,
ROUND(AVG(amount) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS avg_2day,
amount - LAG(amount) OVER w AS change
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sold_on)
ORDER BY region, sold_on;
region sold_on amount running avg_2day change
------ ---------- ------ ------- -------- ------
eu 2026-11-01 100 100 100.0
eu 2026-11-02 100 200 100.0 0
eu 2026-11-03 60 260 80.0 -40
eu 2026-11-04 90 350 75.0 30
us 2026-11-01 80 80 80.0
us 2026-11-02 120 200 100.0 40
us 2026-11-03 120 320 120.0 0
us 2026-11-04 40 360 80.0 -80
LAG returns NULL on the first row of each partition because there's no earlier row; pass a third argument for a default value if NULL isn't what you want. The WINDOW w AS (...) clause saves repeating the partition and order, and OVER (w ROWS ...) adds a frame on top of it. Postgres allows that as long as the named window has no frame of its own. One more Postgres note: the SQL standard's IGNORE NULLS option for LAG, LEAD and friends is not implemented, so skipping NULLs takes a subquery or some extra logic.
Frame units and support differ by engine:
| Postgres 18 | SQLite | MySQL 8.4 | |
|---|---|---|---|
ROWS, RANGE | yes | yes | yes |
GROUPS | yes | yes, since 3.28.0 | not in the frame grammar |
EXCLUDE CURRENT ROW etc. | yes | yes, since 3.28.0 | not in the frame grammar |
The LAST_VALUE trap
SELECT sold_on, amount,
LAST_VALUE(amount) OVER (ORDER BY sold_on) AS default_frame,
LAST_VALUE(amount) OVER (ORDER BY sold_on
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS whole_partition
FROM sales
WHERE region = 'eu'
ORDER BY sold_on;
sold_on amount default_frame whole_partition
---------- ------ ------------- ---------------
2026-11-01 100 100 90
2026-11-02 100 100 90
2026-11-03 60 60 90
2026-11-04 90 90 90
With the default frame, the "last" row is the current one (or its last peer), so LAST_VALUE just echoes the current amount. The Postgres docs warn this is likely to give unhelpful results. Either widen the frame as shown, or use FIRST_VALUE with the order reversed, which works with the default frame.
Gaps and islands: finding streaks
"Longest run of consecutive login days" sounds like it needs a loop. It doesn't. Subtract each row's ROW_NUMBER (as days) from its date: consecutive dates produce the same result, and every gap shifts it.
CREATE TABLE logins (user_id INTEGER NOT NULL, seen_on TEXT NOT NULL, PRIMARY KEY (user_id, seen_on));
INSERT INTO logins VALUES
(1, '2026-11-01'), (1, '2026-11-02'), (1, '2026-11-03'),
(1, '2026-11-06'), (1, '2026-11-07'),
(2, '2026-11-02'), (2, '2026-11-04'), (2, '2026-11-05');
WITH numbered AS (
SELECT user_id, seen_on,
date(seen_on, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY seen_on) || ' days') AS grp
FROM logins
)
SELECT user_id, MIN(seen_on) AS streak_start, MAX(seen_on) AS streak_end, COUNT(*) AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY user_id, streak_start;
user_id streak_start streak_end days
------- ------------ ---------- ----
1 2026-11-01 2026-11-03 3
1 2026-11-06 2026-11-07 2
2 2026-11-02 2026-11-02 1
2 2026-11-04 2026-11-05 2
For user 1, the first three dates minus 1, 2 and 3 days all land on 2026-10-31; the two-day gap moves the next pair to 2026-11-02. That grp value has no meaning of its own. It's just a label that's constant within a streak.
In Postgres, store seen_on as date and replace the date(...) call with seen_on - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY seen_on))::int, since date minus integer gives a date. The same trick works for sequential IDs (subtract the row number from the ID) and for spotting missing invoice numbers.
The primary key matters here: duplicate dates for a user would break the arithmetic. If your source can contain duplicates, SELECT DISTINCT them first or use DENSE_RANK instead of ROW_NUMBER.
Before you ship a window query
- Make
ROW_NUMBERorderings unique. Add the primary key as a final tiebreaker. - Write the frame out whenever you use
SUM,AVG,LAST_VALUEorNTH_VALUEwithORDER BY. Future you won't remember the default. - Filter in an outer query, not
WHERE. - Watch the sort. Rows have to reach the window in partition and sort order, which often means a sort step in the plan. Paste the
EXPLAIN ANALYZEtext into our SQL EXPLAIN visualizer to see where the time goes, and check whether an index on(region, sold_on)lets the planner skip it. - Keep them readable. Long
OVERclauses are where formatting pays off; the SQL formatter handles window syntax, and our SQL formatting standards post covers making one style stick. If you're choosing an engine partly on analytics features, the frame-support table above is one more input next to our PostgreSQL vs MySQL comparison.
Written by AIDToolStack Editorial
Researched from official documentation, specifications and vendor pages, listed under Sources below. Last checked November 2026.
Sources
- sqlite.org/windowfunctions.html
- www.postgresql.org/docs/18/tutorial-window.html
- www.postgresql.org/docs/18/functions-window.html
- www.postgresql.org/docs/18/sql-expressions.html
- dev.mysql.com/doc/refman/8.4/en/window-functions-frames.html
- www.postgresql.org/docs/18/sql-select.html
- www.postgresql.org/docs/18/functions-datetime.html
Related Posts
Explore on AIDToolStack
Discover tools and servers featured in our directory.
AI Tools
MCP Servers
Sponsor Our Newsletter
Reach thousands of developers who are actively evaluating AI tools, MCP servers, and dev infrastructure. Our weekly newsletter goes to engaged technical decision-makers.
All sponsored content is clearly labeled per our editorial policy.