PARTITION BY and ORDER BY

PARTITION BY

Splits the rows into groups; the function restarts in each one.

SUM(total) OVER (PARTITION BY region)
SUM(total) OVER (PARTITION BY region, year)

It is the window equivalent of GROUP BY, except the rows survive.

ORDER BY inside OVER

Adding ORDER BY does more than sort — it changes the default frame:

SUM(total) OVER ()                      -- grand total
SUM(total) OVER (ORDER BY order_date)   -- running total

Without ORDER BY, the frame is the whole partition. With it, the frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — everything up to the current row. That surprises people, and it is a favourite interview follow-up.

Frames explicitly

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- running total
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW           -- 3-period moving window
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   -- remaining total

ROWS versus RANGE

ROWS counts physical rows. RANGE includes every row sharing the current row's ORDER BY value.

With duplicate dates, RANGE pulls in all the peers and ROWS does not — so a three-day moving average can silently include four or five rows. When you mean exactly n rows, say ROWS explicitly.