SUM, AVG and COUNT with OVER.

Ordinary aggregates SUM, AVG, COUNT, MIN and MAX become window functions once you add OVER. Without PARTITION BY they run over the whole result; with PARTITION BY, over the row's own part. That puts the detail next to the summary: a product's price and the average of its category, or an order and its customer's total.

A useful combination is the difference from the average: price - AVG(price) OVER (PARTITION BY category). Round money with ROUND(x, 2). AVG over NUMERIC is NUMERIC; COUNT returns an integer.

Aggregates inside OVER skip NULL (as usual). Rows with a NULL category form their own window — filter them if you do not want them.

Common mistakes: an aggregate without OVER in a query with non-grouped columns (GROUP BY error); missing rounding; PARTITION BY on the wrong column; expecting a row order.

Small example:

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