How is a window function different from GROUP BY?
GROUP BY collapses rows into one row per group. A window function computes over a related set of rows but keeps every original row, so you can show a row's value beside its group's total or its rank.
Explain it simply
A class teacher can announce only the section average (GROUP BY: one line per section), or can write each student's marks plus the section average next to them (window function: every student still has a line). The window does not remove rows; it adds a computed column.
OVER (PARTITION BY ...) defines the group each row looks at.
Example
SELECT name, dept, salary,
avg(salary) OVER (PARTITION BY dept) AS dept_avg,
salary - avg(salary) OVER (PARTITION BY dept) AS diff_from_avg
FROM employees;
-- name | dept | salary | dept_avg | diff_from_avg
-- Asha | IT | 60000 | 55000 | 5000
-- Rohit | IT | 50000 | 55000 | -5000
-- Meera | HR | 40000 | 40000 | 0
SELECT dept, avg(salary)
FROM employees
GROUP BY dept; -- one row per dept onlyWindow functions run after WHERE, GROUP BY and HAVING, so you cannot filter on them directly in WHERE; wrap the query in a subquery or CTE.
What to say in the interview
"GROUP BY reduces to one row per group, while window functions keep all rows and add a value computed over a partition. They are evaluated after WHERE and GROUP BY, so to filter by a window result I use a subquery or CTE."
Common mistakes
- Writing WHERE rank = 1 in the same query level as the window function.