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