PARTITION BY and ORDER BY

PARTITION BY: independent windows side by side

PARTITION BY is the spatial dimension of a window. It chops the result set into groups and computes the window function independently inside each group, resetting at every boundary. Think of each partition as its own private window that knows nothing about the others.

SELECT region, salesperson, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       AVG(amount) OVER (PARTITION BY region) AS region_avg
FROM sales;

Every West row sees only West rows; every East row sees only East. The region_total for a West sale is the sum of all West amounts and nothing else. This is exactly the role GROUP BY region plays in a grouped query — but here the rows are never collapsed. Omit PARTITION BY and the whole result set is one giant partition (a grand total over all rows).

ORDER BY inside OVER: order within the partition

ORDER BY inside the OVER (...) clause is the temporal dimension — it defines the order of rows within each partition. This is completely separate from the query’s outer ORDER BY, which only sorts the final output. The window’s ORDER BY is what makes “running” and “row-relative” computations possible: it gives the rows a sequence, so a function can say “everything up to here,” “the previous row,” “the rank so far.”

SELECT region, sale_date, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_date) AS nth_sale,
       SUM(amount)  OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

ROW_NUMBER() needs the order to number 1, 2, 3 within each region by date. SUM with that same window now produces a running total — and that is the surprise.

The default-frame trap

Here is the rule that bites people. When OVER has no ORDER BY, the window’s frame — the slice of rows the function actually aggregates — defaults to the entire partition. So SUM(amount) OVER (PARTITION BY region) sums every row in the region: one flat number, repeated.

The instant you add ORDER BY inside OVER, the default frame silently changes to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — “from the first row of the partition up to the current row.” That is a running aggregate, not a partition-wide one. Same SUM, same PARTITION BY, but adding an inner ORDER BY flips a flat total into a cumulative one.

-- Flat region total (no inner ORDER BY): same value on every West row
SUM(amount) OVER (PARTITION BY region)

-- Running total (inner ORDER BY): grows down the partition
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date)

That is the bug from the Hook. The author wanted a flat region total but added ORDER BY sale_date (perhaps copied from a neighboring ROW_NUMBER), and the frame quietly became cumulative. The fix is to remove the inner ORDER BY for a flat total, or — if some columns need ordering and others don’t — name the windows explicitly so each gets the frame it wants.