StackKit

SQL Window Functions Explained with Examples

By · · 4 min read

A window function computes a value for each row using a set of related rows, without collapsing the rows the way GROUP BY does. They are the standard tool for running totals, rankings, comparisons with the previous row, and "top N per group" queries.

All queries in this article were run on SQLite 3.54, which supports window functions (SQLite 3.25 or newer). The syntax is standard SQL and works the same way in PostgreSQL, MySQL 8, SQL Server and others.

The sample data

CREATE TABLE sales(
  id INTEGER PRIMARY KEY,
  rep TEXT, region TEXT, sold_on TEXT, amount INTEGER
);

INSERT INTO sales(rep, region, sold_on, amount) VALUES
('Asha','South','2026-01-05',300), ('Asha','South','2026-01-12',500),
('Asha','South','2026-01-20',200), ('Ben','South','2026-01-07',500),
('Ben','South','2026-01-15',100),  ('Chen','North','2026-01-03',700),
('Chen','North','2026-01-18',300), ('Dia','North','2026-01-09',700);

The anatomy of a window function

function(...) OVER (
  PARTITION BY column   -- optional: split rows into groups
  ORDER BY column       -- optional: order rows inside each group
)
  • PARTITION BY is like GROUP BY, but it does not merge rows.
  • ORDER BY inside OVER sets the order the function sees rows in.
  • With no PARTITION BY, the whole result is one group.

Running totals with SUM() OVER

An aggregate function becomes a window function when you add OVER.

SELECT rep, sold_on, amount,
       SUM(amount) OVER (PARTITION BY rep ORDER BY sold_on) AS running_total
FROM sales
WHERE rep IN ('Asha', 'Ben')
ORDER BY rep, sold_on;

rep   sold_on     amount  running_total
Asha  2026-01-05   300     300
Asha  2026-01-12   500     800
Asha  2026-01-20   200     1000
Ben   2026-01-07   500     500
Ben   2026-01-15   100     600

Every row is kept, and each shows the total so far for that rep. With ORDER BY present, the default frame is "from the start of the partition to the current row", which is what makes it a running total.

ROW_NUMBER, RANK and DENSE_RANK

All three number rows by an ordering, and differ only in how they treat ties. Here two sales are tied at 700:

SELECT id, rep, amount,
       ROW_NUMBER() OVER w AS rn,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS drnk
FROM sales
WHERE region = 'North' OR rep = 'Ben'
WINDOW w AS (ORDER BY amount DESC)
ORDER BY amount DESC, id;

id  rep   amount  rn  rnk  drnk
6   Chen  700     1   1    1
8   Dia   700     2   1    1
4   Ben   500     3   3    2
7   Chen  300     4   4    3
5   Ben   100     5   5    4
  • ROW_NUMBER always gives unique numbers. Ties are broken arbitrarily unless you add a tiebreaker to the ORDER BY.
  • RANK gives tied rows the same rank and then skips numbers (1, 1, 3).
  • DENSE_RANK gives tied rows the same rank without gaps (1, 1, 2).

The WINDOW w AS (...) clause names a window so you do not repeat it.

Comparing with the previous row: LAG and LEAD

LAG(column) reads the value from the previous row in the window; LEAD reads the next one.

SELECT sold_on, amount,
       LAG(amount) OVER (ORDER BY sold_on) AS prev,
       amount - LAG(amount) OVER (ORDER BY sold_on) AS change
FROM sales
WHERE rep = 'Asha'
ORDER BY sold_on;

sold_on     amount  prev  change
2026-01-05  300
2026-01-12  500     300   200
2026-01-20  200     500   -300

The first row has no previous row, so prev is NULL. You can pass a default as the third argument: LAG(amount, 1, 0).

Top row per group

A very common task: the best sale in each region. Number the rows within each partition, then filter.

SELECT region, rep, amount
FROM (
  SELECT region, rep, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id) AS rn
  FROM sales
)
WHERE rn = 1;

region  rep   amount
North   Chen  700
South   Asha  500

You must use a subquery (or CTE) because window functions cannot appear in a WHERE clause; the filter has to run on their result. Adding id to the ordering makes the choice deterministic when amounts tie, as they do for Chen and Dia.

Common mistakes

  • Using a window function in WHERE. It is evaluated after filtering. Wrap the query and filter outside.
  • Forgetting a tiebreaker in ROW_NUMBER, which makes results change between runs.
  • Confusing PARTITION BY with GROUP BY. Partitioning keeps all rows.
  • Expecting ORDER BY in OVER to sort the output. It only affects the function; add a normal ORDER BY for the final result.
  • Ignoring the frame. With ORDER BY, aggregate window functions default to a running frame. To total the whole partition, omit ORDER BY from the OVER clause.

Summary

Window functions let you add per-row analytics (totals, ranks, previous values) alongside the original rows. Learn the three parts, PARTITION BY, ORDER BY and the function, and most reporting queries become a single statement instead of a self-join.

Keep reading