How do window functions differ from GROUP BY?

Detailed explanation

Both operate on groups of rows, but they return different shapes.

• GROUP BY produces one row per group. Every selected column must be grouped or aggregated.
• A window function (... OVER (PARTITION BY ...)) produces one row per input row. The partition only defines which rows feed the calculation.

Example

-- One row per department
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept;

-- One row per employee, with the department average alongside
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 answers “how does each person compare with their department?”, which GROUP BY alone cannot do without joining the aggregate back to the detail table.

When to use which

Need	Use
Totals or averages per group	GROUP BY
Rank within a group, top N per group	Window (ROW_NUMBER, RANK)
Running total, moving average	Window with a frame
Compare to the previous row	Window (LAG, LEAD)

Common mistakes

1. Selecting a non-aggregated column with GROUP BY (an error in standard SQL).
2. Filtering on a window result in WHERE. Window functions are evaluated after WHERE, so filter in an outer query or CTE, or use QUALIFY where supported.
3. Saying window functions are “faster”. They solve a different problem; performance depends on the engine and data.