Window Functions Explained by Example

The question GROUP BY cannot answer

"Show me every employee alongside the average salary in their department."

Try it with GROUP BY and you hit a wall immediately. Grouping collapses rows to one row per department, but the question wants all twenty employees and a per-department number attached to each. You end up writing a subquery, joining it back, and repeating yourself.

Window functions solve exactly this: they compute across a set of related rows without collapsing them. Every input row still comes out.

Twenty rows in, twenty rows out, each carrying its department's average.

The anatomy of OVER

Every window function has the same shape.

FUNCTION(argument) OVER (
  PARTITION BY grouping_column   -- split rows into independent windows
  ORDER BY sort_column           -- order rows inside each window
  ROWS BETWEEN ... AND ...       -- which rows in the window to include
)

PARTITION BY is the window equivalent of GROUP BY, but it divides rather than collapses. ORDER BY inside OVER decides the sequence within each partition, which matters for anything cumulative or positional. Leave both out and the window is the entire result set.

Ranking inside a partition

The classic use: highest earner per department. ROW_NUMBER() numbers the rows in each partition according to the window's ORDER BY.

To keep only the top earner per department you cannot add WHERE rank_in_dept = 1, because window functions are evaluated after WHERE, so the alias does not exist yet. Wrap the query and filter outside it.

Running totals, and the frame that ruins them

A cumulative revenue line is a window with an ORDER BY and no partition.

Look closely at the two orders on the same date. Their running totals are identical: the total jumps past both at once instead of stepping through them. That is not a bug, it is the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, where "current row" means every row with the same sort value as the current row. Peers are treated as a single step.

If you want a strict row-by-row accumulation, say ROWS instead of RANGE.

The same frame syntax gives you moving averages. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-point trailing average; ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING is a centred one.

Comparing a row to its neighbour

LAG reaches backwards in the window, LEAD forwards. Month-over-month growth is a GROUP BY for the monthly totals, then a LAG over the result.

The first row's prev_month is NULL because there is nothing before it. Wrap the expression in COALESCE if a zero reads better in your report.

Where windows fit in the query

Order of evaluation, which explains most window function errors:

1. FROM and JOIN assemble the rows
2. WHERE filters them
3. GROUP BY and HAVING aggregate them
4. Window functions run here, over what survived
5. SELECT projects, ORDER BY sorts, LIMIT truncates

Two consequences worth memorising. Window functions can be applied on top of aggregates: SUM(revenue) OVER (...) in a query that already has a GROUP BY is legal and useful. And they can never be referenced in WHERE or HAVING; you need a CTE or a subquery, as in the top-earner example above.

Where to go next

Try rewriting a self-join you already have. Almost any query that joins a table to itself to compare a row with the previous one, or to a group summary, is a window function waiting to be simplified: usually shorter, usually faster, and read once instead of twice.