What Does PARTITION BY Change?

It splits the rows into independent windows, so that each row's window contains only the rows in its own partition. Without PARTITION BY, every row's window is the entire result set.

Think of it as a GROUP BY that does not reduce the row count. GROUP BY puts rows into groups and hands you one summary row per group. PARTITION BY puts rows into groups and hands you every row back, each carrying a summary of the group it belongs to. That is the difference in a sentence.

The three things that follow from it. A ranking function restarts at 1 for each partition, so ROW_NUMBER over a partition is a per-group sequence rather than a global one. An aggregate sums only within the partition, so a running total resets at each partition boundary. And the frame is applied within the partition, so a moving average never reaches across a group boundary.

Two details. The PARTITION BY expression does not have to match a GROUP BY grouping — it can be any expression the engine can evaluate per row, including one that is not selected. And the partitions are independent, which means the engine can compute them in parallel, so a well-partitioned window function is often as fast as the equivalent GROUP BY even though it returns more rows.

Put customer_id in the PARTITION BY and the ranking restarts for every customer.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 1,  90.00);

SELECT id,
       customer_id,
       amount,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS amount_rank
FROM orders
ORDER BY customer_id, amount_rank;

Four rows come back. Customer 1's orders are ranked 1, 2 and 3 by amount — 340.50, then 120.00, then 90.00 — and customer 2's single order is ranked 1. Without the PARTITION BY, customer 2's order would have been ranked 3, because the ranking would have been over all four rows.

That is the whole effect of the clause, and it is the difference between a global leaderboard and a per-group one. The same function, the same data, a different set of rows in the window.

One practical point about RANK here. If two of a customer's orders tied on amount, they would share a rank and the next order would skip a number. If what you want is a dense sequence with no gaps, use DENSE_RANK, or ROW_NUMBER with a tiebreaker on the id so the numbering is completely deterministic.

An aggregate window function with a partition gives every row its group's average, and subtracting gives the difference.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 1,  90.00);

SELECT id,
       customer_id,
       amount,
       ROUND(AVG(amount) OVER (PARTITION BY customer_id), 2) AS group_avg,
       ROUND(amount - AVG(amount) OVER (PARTITION BY customer_id), 2) AS vs_group_avg
FROM orders
ORDER BY customer_id, id;

Four rows come back. Ada's three orders each carry her group average of 183.50 and differences of -63.50, 157.00 and -93.50. Grace's single order carries an average of 75.25 and a difference of 0.00 — which is the correct answer, since a group of one is always exactly at its own average.

Two details. The AVG has no ORDER BY inside the window, which means the default frame applies and every row in the partition sees the whole partition. That is what you want here — a group average, not a running average. Add ORDER BY and you get a running average instead, which is a different number entirely.

The other detail is the NULL case. If a partition contains only NULL amounts, AVG returns NULL and the difference becomes NULL for every row in that group, so the group silently disappears from the comparison rather than showing zeros. If that matters, wrap the AVG in COALESCE with an explicit fallback so the report says something rather than nothing.

This pattern is the window-function equivalent of the classic "compare to the group average" request, and it is also how you compute a z-score without leaving SQL: subtract the group average and divide by the group standard deviation, both as partitioned window functions.