What is the difference between PARTITION BY in a window function and GROUP BY in an aggregate query?

How to think about it

What the question is really testing

This is the most important conceptual distinction in window functions. Interviewers ask it because many analysts know how to write OVER (PARTITION BY ...) but haven’t internalized why it preserves rows — and that understanding drives correct query design.

The one-sentence distinction

GROUP BY collapses rows. PARTITION BY does not.

GROUP BY — one output row per group

SELECT
  department_id,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- Returns one row per department; individual employee data is gone

You cannot see each employee’s salary alongside the department average in the same query without a join or subquery.

PARTITION BY — every row preserved

SELECT
  employee_id,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;
-- One row per employee, each with their dept's average alongside

The aggregate is computed per partition, but no rows are removed. This is what makes window functions powerful for comparisons and ratios.

Try it live — compare the two side by side

Combining both in one query

You can use GROUP BY and window functions together when you need per-group summaries that themselves get ranked:

SELECT
  department_id,
  AVG(salary)                                       AS dept_avg,
  RANK() OVER (ORDER BY AVG(salary) DESC)           AS dept_rank
FROM employees
GROUP BY department_id;

Here GROUP BY reduces to one row per department, and then RANK() operates over those aggregated rows to rank departments by average pay.

Quick reference

Need	Use
One summary row per group	GROUP BY
Row-level data alongside a group statistic	Window function with PARTITION BY
Group-level data that itself needs ranking/comparison	GROUP BY + window function