Window function vs GROUP BY

Question

Accepted Answer

GROUP BY reduces N rows to one row per group - you lose access to individual row data. Window functions retain every source row and attach the aggregate as an extra column. Query A: 1 row per dept showing the department average. Query B: 1 row per employee showing their name, dept, AND the dept average - letting you immediately compare individual vs. group. This is why you cannot mix a window function and GROUP BY to do the same thing; they operate at different levels of granularity.