GROUP BY vs PARTITION BY: what is the actual difference?

You had 200 rows. Now you have 3, and the query that tried to keep the names threw an error instead:

SELECT dept, name, AVG(salary)
FROM employees
GROUP BY dept;
-- ERROR: 'name' must appear in GROUP BY

The error is the engine telling you that you asked two incompatible questions at once. GROUP BY answers one of them. PARTITION BY answers the other.

Two different questions

"What is the average salary per department?" is a question about groups. Detail rows are irrelevant to the answer, so GROUP BY deletes them:

SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept;
-- 200 employees -> 3 rows

"How does each person compare to their department?" is a question about rows in context. Every row must survive, so it needs a window:

SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS vs_dept
FROM employees;
-- all 200 rows, each with its context

This second query is an interview classic precisely because it filters out the candidates who only know GROUP BY: without windows, it takes a self-join.

The rule of thumb

Listen to the question's grammar. The word "per" almost always means GROUP BY. The phrases "vs its", "compared to", "share of" almost always mean PARTITION BY. When the request needs both shapes, aggregate first, then window over the result.

The window-filter trap

The natural next step fails:

-- broken: WHERE runs before windows exist
WHERE salary > dept_avg

-- works: materialize, then filter
WITH ranked AS (
  SELECT ..., AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM employees
)
SELECT * FROM ranked
WHERE salary > dept_avg;

Same execution-order story as WHERE vs HAVING (sheet #018): the clause you are filtering in runs before the value you are filtering on exists. The CTE wrapper is the standard fix everywhere; QUALIFY is the shortcut on the engines that have it.

The takeaway

GROUP BY collapses, PARTITION BY keeps. Pick by the shape of the answer the question needs, and filter windows one layer out. The print-ready PDF above has both queries and the trap on one page.