SQL window functions: OVER, PARTITION BY, and running totals

A window function computes a value across a set of rows that are related to the current row, without collapsing those rows into one. That last part is the whole point and the key difference from GROUP BY. An aggregate with GROUP BY returns one row per group. A window function returns a value for every original row while still seeing the group. You keep the detail and get the summary alongside it.

The OVER clause

Any aggregate becomes a window function when you add OVER. Compare these two:

SELECT dept, AVG(salary) FROM employees GROUP BY dept;        -- one row per dept
SELECT name, dept, AVG(salary) OVER (PARTITION BY dept)        -- every row, plus the dept average
FROM employees;

The first gives you the average per department and nothing else. The second gives you every employee, each with their department's average sitting next to their own salary, so you can compare an individual to their group in a single pass. That comparison is awkward with GROUP BY because the detail row and the aggregate are in different result shapes.

PARTITION BY and ORDER BY

Inside OVER you can supply two things:

• PARTITION BY divides rows into groups, the window. The function restarts for each partition. Omit it and the whole result set is one window.
• ORDER BY orders rows within the partition, which matters for ranking functions and for running totals, because "running" means "up to and including this row in this order".

SELECT name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
FROM employees;

Every employee gets their rank within their own department, highest paid first.

The ranking functions

Three functions look similar and differ in how they handle ties:

Use ROW_NUMBER() to pick exactly one row per group, for example the single most recent order per customer. Use RANK() when the gap after a tie is meaningful, as in competition standings. Use DENSE_RANK() when you want contiguous rank levels.

Running totals and moving windows

With ORDER BY inside OVER, an aggregate accumulates:

SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Each row shows the sum of all amounts up to and including that date. You can narrow the window further with a frame clause such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a seven row moving sum. A subtle default to know: when you add ORDER BY without an explicit frame, most engines use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and rows with an equal ordering value are lumped together, which can surprise you with running totals over duplicate dates. Specify ROWS if you want strict row by row behavior.

Where you can use them

Window functions are evaluated after WHERE, GROUP BY, and HAVING, but before the final ORDER BY. The practical consequence is that you cannot reference a window function in a WHERE clause of the same query, because it has not been computed yet. The standard workaround is to wrap the query in a CTE or subquery and filter on the result:

WITH ranked AS (
  SELECT name, dept,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn = 1;   -- top earner per dept

The mental model

GROUP BY folds rows together; a window function looks sideways at neighboring rows and keeps every row intact. Whenever you catch yourself joining a table back to its own aggregate, a window function is usually the shorter, faster answer.

To keep a query with several nested windows legible, run it through the SQL Formatter, which indents it locally in your browser.