OVER (PARTITION BY ...).

PARTITION BY splits rows into independent windows — like groups, without collapsing. The aggregate is computed separately for each window and the value is returned on every row of it: COUNT(*) OVER (PARTITION BY customer_id) gives each order the number of orders of its customer.

You can partition by an expression, for example by month: AVG(total) OVER (PARTITION BY date_trunc('month', order_date)). Rows with NULL in the key form one shared window, so we often filter WHERE customer_id IS NOT NULL (WHERE runs before the window and decides which rows take part).

Result order is not guaranteed without ORDER BY.

Common mistakes: PARTITION BY without OVER; confusing PARTITION BY with GROUP BY (it does not collapse rows); forgetting that NULL keys share one window; expecting a particular row order.

Small example:

SELECT id, customer_id, total, MAX(total) OVER (PARTITION BY customer_id) AS best_order FROM orders WHERE customer_id IS NOT NULL;