Databases

SQL Window Functions Explained: From ROW_NUMBER to Running Totals

A hands-on guide to ranking, running totals, LAG/LEAD, moving averages and top-N per group, with runnable PostgreSQL examples and outputs.

·
10 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

SQL Window Functions Explained: From ROW_NUMBER to Running Totals

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 BY splits the rows into independent groups (the "windows"). Leave it out and the whole result set is one group.

  • ORDER BY sorts 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 why id is in the ORDER BY above. 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

UNBOUNDED PRECEDING

from the first row of the partition

n PRECEDING

n rows before the current row

CURRENT ROW

the current row

n FOLLOWING

n rows after the current row

UNBOUNDED FOLLOWING

to the last row of the partition

Two defaults are worth memorising:

  1. No ORDER BY in the OVER clause: the frame is the whole partition.

  2. With ORDER BY and no explicit frame: the frame is RANGE 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

  • ROWS counts physical rows. "2 PRECEDING" means exactly two rows back.

  • RANGE works on values of the ORDER BY column, 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 BY and ORDER BY columns together, for example (region, sale_date).

  • Reuse the same OVER definition where you can. PostgreSQL can compute several functions that share a window in one pass over one sort, while a window with a different PARTITION BY or ORDER BY may need a sort of its own.

  • Check the plan with EXPLAIN ANALYZE. In PostgreSQL, look for the WindowAgg node and any Sort feeding it.

  • Filter rows in WHERE first. Fewer rows entering the window means less sorting.

Cheat sheet

Function

What it does

ROW_NUMBER()

Unique sequential number per row

RANK()

Rank with gaps after ties

DENSE_RANK()

Rank without gaps

NTILE(n)

Splits the partition into n buckets

LAG(col, n, default)

Value from n rows before

LEAD(col, n, default)

Value from n rows after

FIRST_VALUE(col) / LAST_VALUE(col)

First / last value in the frame

NTH_VALUE(col, n)

The nth value in the frame

SUM / AVG / COUNT / MIN / MAX

Aggregate over the frame

PERCENT_RANK() / CUME_DIST()

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!

Anshu Pathak

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Anshu Pathak's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.