GROUP BY has a limitation everyone runs into: it collapses rows. You counted orders per customer — you see the totals but lost the orders themselves. Yet tasks constantly demand both: "show every order and next to it — which one it is in this customer's sequence," "output each customer's latest order." That's what window functions are for — aggregates that compute over a group but don't collapse the rows.

OVER: an aggregate without collapsing

Compare. A regular aggregate:

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;
-- 2 rows: customer → how many orders

The window variant:

SELECT id, customer_id, amount,
       COUNT(*) OVER (PARTITION BY customer_id) AS orders_of_customer
FROM orders;
-- all orders stay, each annotated with "how many this customer has in total"

The OVER keyword turns a function into a window function: it looks at a "window" — the set of rows around the current one — and attaches the result to the row without removing it. PARTITION BY customer_id sets the window's boundaries: compute within one customer. Same meaning as GROUP BY, minus the row loss.

ROW_NUMBER: numbering within a group

The most used window function is ROW_NUMBER(): it numbers rows within the window in a given order.

SELECT id, customer_id, created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

The ORDER BY created_at DESC inside OVER sets the numbering order: the customer's freshest order gets number 1, the next one 2, and so on — each customer has their own numbering.

Hence the solution to the classic "latest record per group" task, which is painful without windows:

SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn = 1;

We numbered each customer's orders from newest to oldest — and kept only the first ones. The outer wrapper is needed because you can't filter by a window function directly in WHERE: the window is computed after it.

Nearby live RANK() and DENSE_RANK() — ranks that honor ties: on equal values ROW_NUMBER assigns numbers arbitrarily (1, 2), RANK gives both first place and skips (1, 1, 3), DENSE_RANK doesn't skip (1, 1, 2).

Running totals and the row next door

Two more everyday moves:

  • A running total — SUM with ORDER BY inside the window:
SELECT id, created_at, amount,
       SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

Each row receives the sum of all orders up to and including its date — a cumulative revenue chart in one line.

  • Comparing with the previous row — LAG (and its mirror LEAD for the next one):
SELECT id, created_at,
       created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS since_prev
FROM orders;

LAG pulls the value from the window's previous row — and you see how much time passed between a customer's orders. Hunting anomalies in time series (duplicates a second apart, suspicious gaps) is done exactly this way.

Where this applies

Window functions are the line between "I write queries" and "I converse with the database fluently." Tasks that otherwise require an Excel export or towers of subqueries become one-liners: the latest status of every application, top-3 products per category, time between a user's events, duplicates as "keep the first, find the rest" (rn > 1). In data checks it's the workhorse: ROW_NUMBER … WHERE rn = 1 is the canonical way to take the current record from a history table.

Where beginners stumble:

  • They confuse the window's ORDER BY with the query's ORDER BY. Inside OVER it's the computation order (numbering, accumulation); at the end of the query it's the display order. They're independent.
  • They try to filter by the window in WHERE (WHERE ROW_NUMBER() … = 1) — an error: wrap it in a subquery and filter outside.
  • They use ROW_NUMBER where ties matter. Two orders in the same second — a random one becomes "the latest." Either refine the ORDER BY (add id) or deliberately take RANK.

What to learn next. Windows constantly bump into empty values — the right time to sort them out: NULL and data types.