Unit 07.01: PARTITION BY: resetting the calculation per group

Unit ID: SQL-M07-U02 - Estimated active time: 13-16 minutes Objective: control the set of rows a window function sees.

PARTITION BY draws the boundary

OVER () means every row. OVER (PARTITION BY status) restarts the calculation for each status:

SELECT status,
       COUNT(*)  OVER ()                      AS all_orders,      -- 1000
       COUNT(*)  OVER (PARTITION BY status)   AS status_orders    -- 988 or 12
FROM orders
LIMIT 3;

Both columns appear on the same row. One counts the whole table, the other counts only rows sharing that status. This is what makes "row versus group versus total" comparisons possible in a single query.

It is not the same as GROUP BY

They use similar words and do different jobs:

A genuinely useful shape

Comparing each order against its own group's average:

SELECT order_id,
       status,
       order_total,
       ROUND(AVG(order_total) OVER (PARTITION BY status), 2) AS status_avg,
       ROUND(order_total - AVG(order_total) OVER (PARTITION BY status), 2) AS diff_from_avg
FROM orders
ORDER BY diff_from_avg DESC
LIMIT 3;

Every order now knows how far it sits from its peers. Doing this with GROUP BY requires computing the averages separately and joining them back.

Partitioning by a nullable column

SELECT COUNT(DISTINCT country) FROM customers;   -- 4

There are four non-NULL countries, but PARTITION BY country produces five partitions - NULL forms its own. Unlike a WHERE comparison, partitioning treats all NULLs as one group rather than dropping them. That is usually helpful, and it is worth knowing before the numbers surprise you.

Practice

Write a query showing, for each customer country, the number of customers in that country alongside the total customer count - without using GROUP BY.

SELECT DISTINCT
       COALESCE(country, 'Unknown')            AS country,
       COUNT(*) OVER (PARTITION BY country)    AS in_country,
       COUNT(*) OVER ()                        AS all_customers
FROM customers
ORDER BY in_country DESC;
-- IN 2813 | GB 902 | AE 516 | Unknown 300 | SG 281, all_customers 4812

Takeaway

PARTITION BY sets the window's boundary. NULLs form their own partition rather than disappearing - another place where windows and filters behave differently.

---

Mark complete