Window Functions: When to Use OVER() Instead of GROUP BY

Window functions let you perform calculations across rows while keeping individual row details. GROUP BY collapses rows.

The Key Difference

GROUP BY aggregates rows into groups (reduces row count):

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
-- Returns one row per department

Window Functions calculate across rows while preserving all rows:

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;
-- Returns all employee rows with department average

When to Use Window Functions

• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
• Running totals: SUM() OVER (ORDER BY date)
• Moving averages: AVG() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
• Comparisons: Show each row's value vs. group average

Practical Example: Top Salaries by Department

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
WHERE rank <= 3;
-- Get top 3 earners per department