How is a window function different from GROUP BY?
GROUP BY collapses rows into one row per group. A window function computes an aggregate or rank over a set of related rows but keeps every original row in the output.
Explain it simply
GROUP BY is like announcing only the section-wise average marks: 10 sections, 10 lines. A window function is like writing next to each student's own mark the average of their section. All students stay visible, and each carries the group figure alongside.
Example
Table employees(name, dept, salary).
-- GROUP BY, one row per dept
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept;
-- window, one row per employee
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;The second query lets you see each person and the department average together without a join back to a grouped subquery.
Window functions cannot be used directly in WHERE or HAVING, because they run after those clauses. Wrap the query in a subquery or CTE to filter on them.
What to say in the interview
"GROUP BY reduces rows, a window function does not. OVER defines the window with PARTITION BY and ORDER BY. I use windows for rankings, running totals and comparing a row to its group."
Common mistakes
- Trying to put a window function in WHERE.