In the aggregations lesson, GROUP BY collapsed many rows into one summary row per group — and the detail rows were gone. A window function does the same kind of calculation but keeps every row, attaching the result alongside the original columns. "Each employee's salary and their department's average" in one query, no collapsing.
The seed is a small company: a dozen employees across three departments, with a salary tie hidden in each one — we'll need those ties later.
SELECT * FROM employees ORDER BY department, salary DESC;
Aggregates collapse — windows don't
With GROUP BY, twelve employees become three rows. The averages are there, but the people are gone:
SELECT department, round(avg(salary)) AS avg_salary
FROM employees
GROUP BY department
ORDER BY department;
Now the same avg, but written as a window function. OVER () tells Postgres: compute the aggregate over a window of rows, and stamp the result on each row instead of collapsing them. Empty parentheses mean "the window is the whole result set":
SELECT name, department, salary,
round(avg(salary) OVER ()) AS company_avg
FROM employees
ORDER BY salary DESC;
Twelve rows in, twelve rows out — each one carrying the company-wide average. That's the whole trick: any aggregate followed by OVER (…) becomes a window function.
PARTITION BY: per-group values on every row
OVER () used one big window. PARTITION BY splits the rows into groups — like GROUP BY, but without the collapse. Each row sees the aggregate of its own partition:
SELECT name, department, salary,
round(avg(salary) OVER (PARTITION BY department)) AS dept_avg,
salary - round(avg(salary) OVER (PARTITION BY department)) AS diff
FROM employees
ORDER BY department, salary DESC;
Every engineer is compared to the engineering average, every salesperson to the sales average. Queries like "each row versus its group" are awkward with GROUP BY (you'd join the table back onto its own aggregate) — with a window, it's one expression.