Aggregate Window Functions

You know SUM, AVG, COUNT as aggregate functions with GROUP BY. But they also work as window functions with OVER().

The key difference: GROUP BY collapses rows, OVER() keeps every row.

Running total:

SELECT first_name, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

Department average alongside each row:

SELECT first_name, salary, department_id,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

Now you can compare each salary to the department average - without a subquery!

Count within partition:

SELECT department_id, first_name,
       COUNT(*) OVER (PARTITION BY department_id) AS dept_size
FROM employees;

This is one of the most powerful SQL patterns for analytics.