Window functions

The idea

GROUP BY answers a question about a group and then throws the group away: seven orders in, two rows out. A window function asks the same kind of question but leaves every row standing. OVER (PARTITION BY region ORDER BY day) draws a window around each row's group, computes inside it, and writes the answer into a new column beside the row: seven rows in, seven rows out, just wider.

The only genuinely tricky part is deciding which rows are inside the window for a given row. That is what ORDER BY and the frame clause quietly control, and it is where most surprises live.

See it run

SELECT order_id, region, day, amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY day) AS pos,
  SUM(amount)  OVER (PARTITION BY region ORDER BY day
                     RANGE BETWEEN UNBOUNDED PRECEDING
                                   AND CURRENT ROW)  AS run
FROM orders;

How it works

The engine does three things, in order. It partitions the rows, sorts each partition by the window's ORDER BY, then walks each partition once, evaluating the function for every row against that row's frame.

Ranking functions ignore the frame entirely: they only look at the sort key. ROW_NUMBER counts positions; RANK is how many rows sort strictly before me, plus one; DENSE_RANK is how many distinct values I have seen, including mine. Aggregates like SUM do use the frame, and the default when you write ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means every row whose sort value is ≤ mine, peers included.

SELECT
  order_id, region, day, amount,
  ROW_NUMBER() OVER w                      AS rn,
  RANK()       OVER w                      AS rnk,
  DENSE_RANK() OVER w                      AS drnk,
  SUM(amount)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                AND CURRENT ROW)  AS run_rows,
  SUM(amount)  OVER w                      AS run_range   -- implicit RANGE
FROM orders
WINDOW w AS (PARTITION BY region ORDER BY day)
ORDER BY region, day, order_id;   -- the output order is a separate decision

The named WINDOW clause works in PostgreSQL, MySQL 8, SQLite 3.28+ and DuckDB; elsewhere just repeat the OVER (...) text. Here is the same semantics as plain Python, which makes the frame difference hard to miss:

def windowed(rows):
    out = []
    for region in dict.fromkeys(r["region"] for r in rows):        # PARTITION BY
        part = sorted((r for r in rows if r["region"] == region),
                      key=lambda r: r["day"])                      # ORDER BY
        for i, row in enumerate(part):
            peers = [j for j, x in enumerate(part)
                     if x["day"] == row["day"]]                    # the tie group
            out.append({**row,
                "row_number": i + 1,
                "rank":       peers[0] + 1,
                "dense_rank": len({x["day"] for x in part[:i + 1]}),
                "run_rows":   sum(x["amount"] for x in part[:i + 1]),
                "run_range":  sum(x["amount"] for x in part[:peers[-1] + 1]),
            })
    return out          # len(out) == len(rows) — nothing collapsed

Cost

The trade-off: a window returns all n rows where GROUP BY returns one per group, so the output (and the memory holding it) is bigger. You buy that with one sorted pass instead of a self-join or a correlated subquery, usually a very good deal.

Watch out for

• The default frame is RANGE, not ROWS. SUM(x) OVER (ORDER BY day) silently means RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so every row tied on day reports the day's closing total. If you meant "the total through my own row", write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and stop thinking about it.
• ORDER BY inside OVER changes the meaning, not just the order. Without it, the frame is the whole partition, so SUM(amount) OVER (PARTITION BY region) is a group total repeated on every row. Add ORDER BY day and the same expression becomes cumulative. Both are useful; know which one you asked for.
• ROW_NUMBER over a non-unique key is nondeterministic. Two rows tied on day can swap places between runs: different plan, parallel workers, new statistics. If a number decides who gets paid, add a real tiebreaker: ORDER BY day, order_id.
• RANK spends numbers on ties. Ten rows tied at 1 means the next distinct value is rank 11. So rnk <= 3 can return ten rows, drnk <= 3 returns the three best values (any number of rows), and rn <= 3 returns exactly three rows. Pick by what a tie should cost.
• You cannot filter on a window function in WHERE, GROUP BY or HAVING. Windows are evaluated after those clauses and before ORDER BY/LIMIT. Wrap the query in a CTE or subquery and filter outside it, or use QUALIFY where your engine has it. And note the window's ORDER BY does not sort your result set; add a top-level ORDER BY if the output order matters.

Worked example

Take the seven orders in the demo: one row per order, and a dashboard that wants two things per region. The sequence of orders, and how revenue accumulates.

For the sequence, ROW_NUMBER() OVER (PARTITION BY region ORDER BY day, order_id) gives north 1, 2, 3, 4; the extra order_id makes the two tue orders deterministic instead of leaving it to the planner. If the question is instead "orders from the first two active days", DENSE_RANK() ... ORDER BY day gives 1, 2, 2, 3 and <= 2 returns all three mon-and-tue orders. RANK would give 1, 2, 2, 4: same rows here, but the 4 tells you a tie ate a number.

For revenue, the default RANGE frame makes o2 and o3 both read 450: the closing total for tuesday. Plot that and two points sit on top of each other; join it back to a per-order table and tuesday's revenue is counted twice. Under ROWS, o2 reads 300 and o3 reads 450, one step per order. Neither is wrong: RANGE answers "revenue through the end of my day", ROWS answers "revenue through my order". Decide which sentence you meant, then write the frame down so the next reader does not have to guess.

Check yourself