Skip to content
Tutorials

SQL Window Functions: ROW_NUMBER, Running Totals, Frames and Gaps-and-Islands

AIDToolStack EditorialNovember 7, 20268 min read
SQLPostgreSQLSQLiteAnalytics
Share:Twitter

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.

Diagram comparing the ROWS frame, which stops at the current row, with the default RANGE frame, which extends to the last peer row
Diagram comparing the ROWS frame, which stops at the current row, with the default RANGE frame, which extends to the last peer row

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 18SQLiteMySQL 8.4
ROWS, RANGEyesyesyes
GROUPSyesyes, since 3.28.0not in the frame grammar
EXCLUDE CURRENT ROW etc.yesyes, since 3.28.0not 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_NUMBER orderings unique. Add the primary key as a final tiebreaker.
  • Write the frame out whenever you use SUM, AVG, LAST_VALUE or NTH_VALUE with ORDER 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 ANALYZE text 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 OVER clauses 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.
AE

Written by AIDToolStack Editorial

Researched from official documentation, specifications and vendor pages, listed under Sources below. Last checked November 2026.

Sources

  1. sqlite.org/windowfunctions.html
  2. www.postgresql.org/docs/18/tutorial-window.html
  3. www.postgresql.org/docs/18/functions-window.html
  4. www.postgresql.org/docs/18/sql-expressions.html
  5. dev.mysql.com/doc/refman/8.4/en/window-functions-frames.html
  6. www.postgresql.org/docs/18/sql-select.html
  7. www.postgresql.org/docs/18/functions-datetime.html

Related Posts

Explore on AIDToolStack

Discover tools and servers featured in our directory.

MCP Servers

Trigger.dev Jobs MCP ServerManage Trigger.dev background jobs from your AI coding assistant. Monitor job runs, trigger tasks manually, inspect execution logs, manage schedules, and debug failed runs. The server provides operational visibility into your background job infrastructure — see which tasks are running, queued, or failed, and take action. Your AI can help diagnose job failures from execution logs, retry failed tasks, and optimize concurrency configurations for better throughput.Tailscale Network MCP ServerManage your Tailscale mesh VPN network from your AI coding assistant. View connected devices, manage ACLs, configure exit nodes, check device status, and manage DNS settings. The server provides visibility into your Tailscale network — see which machines are online, troubleshoot connectivity issues, manage network access policies, and configure routes. Useful for developers managing distributed infrastructure where Tailscale provides secure networking between services.Portainer Docker MCP ServerManage Docker environments through Portainer's API from your AI coding assistant. Control containers, images, networks, and volumes across multiple Docker hosts and Kubernetes clusters. The server provides tools for container lifecycle management, image pulling and building, stack deployment, and resource monitoring. Portainer's multi-environment management makes this server particularly valuable for teams managing containers across development, staging, and production environments.

Reviews

Sign in to leave a review.

No reviews yet. Be the first!

Comments

Sign in to leave a comment.

No comments yet. Be the first!

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.

Growing subscriber baseHigh open rates

All sponsored content is clearly labeled per our editorial policy.