SQL Window Functions: Aggregates Without Losing Your Rows

Quick Answer

A window function computes across a set of rows related to the current row, without collapsing them. GROUP BY returns one row per group; a window function returns every row with the aggregate attached.

The difference from GROUP BY, measured

Given five sales rows across two regions:

SELECT region, SUM(amount) FROM sales GROUP BY region;
-- [('North', 1500), ('South', 1300)]   -- 2 rows

The individual sales are gone. You cannot see who made which sale, because grouping collapsed them.

SELECT region, rep, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;
-- 5 rows, each carrying its region total

Same totals, every row preserved. That is the entire point: a window function adds a column instead of removing rows.

It is what you need for "show each sale alongside its share of the regional total" — a question GROUP BY cannot answer in one query, because it needs both levels of detail at once.

Ranking within groups

SELECT region, rep, amount,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
  SUM(amount) OVER (PARTITION BY region)               AS region_total
FROM sales ORDER BY region, rnk;

North  Meera  700  1  1500
North  Asha   500  2  1500
North  Ravi   300  3  1500
South  Iqbal  900  1  1300
South  Zoya   400  2  1300

Ranking restarts at 1 for each region, because PARTITION BY region divides the rows into independent windows. ORDER BY inside the OVER clause decides the ranking order — it is separate from the query's own ORDER BY.

Three ranking functions, and the difference is examined constantly:

• ROW_NUMBER() — always 1, 2, 3, even for ties.
• RANK() — ties share a rank and the next value skips: 1, 2, 2, 4.
• DENSE_RANK() — ties share, no gap: 1, 2, 2, 3.

"Top 3 per category" is the classic problem: rank in a subquery, then filter on the rank in the outer query. You cannot filter on a window function in WHERE, because windows are computed after WHERE runs.

Running totals

SELECT rep, amount,
  SUM(amount) OVER (ORDER BY amount
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM sales ORDER BY amount;

Ravi   300   300
Zoya   400   700
Asha   500  1200
Meera  700  1900
Iqbal  900  2800

Each row's value plus everything before it. The frame clause defines which rows are in the window: from the start of the partition (UNBOUNDED PRECEDING) up to the current row.

Change the frame and you get different measures. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three-row moving average — the standard way to smooth a noisy time series in SQL.

Adding ORDER BY inside OVER without an explicit frame gives a running total by default, which surprises people expecting the partition total. Be explicit when it matters.

LAG and LEAD: comparing to the previous row

These solve a problem that is genuinely awkward without them: comparing each row to its neighbour.

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

LAG gives the previous row's value, LEAD the next one's. Month-on-month growth, time between consecutive events, and detecting gaps in a sequence all become one line.

The alternative is a self-join on month = month - 1, which is slower, harder to read, and breaks whenever the sequence has gaps.

The first row's LAG is NULL since nothing precedes it, so wrap in COALESCE if you need a number.

Practical notes

• Window functions run after WHERE, GROUP BY and HAVING. That is why filtering on one requires a subquery or CTE — see CTEs explained.
• They are widely supported — PostgreSQL, MySQL 8+, SQL Server, Oracle and SQLite all have them. MySQL only gained them in version 8, which is why older tutorials avoid them.
• An empty OVER () is valid and means the whole result set, which is handy for "this row's share of the grand total".
• Performance is usually good, but sorting is involved. Indexing the partition and order columns helps, as with any sort — see database indexing.

In interviews these appear as "second highest salary", "top N per group" and "running total". All three are one window function, and candidates who reach for correlated subqueries instead give noticeably weaker answers.

Frequently Asked Questions