OVER & PARTITION BY
Overview
Window functions let you perform aggregate-like calculations (like SUM or AVG) without grouping rows into a single output row. The OVER clause defines a 'window' of rows for each row to compute over, and PARTITION BY splits that window into subgroups. Adding an `ORDER BY` inside the `OVER()` clause changes the window frame to compute running totals (e.g., `SUM(amount) OVER (ORDER BY date)` calculates a cumulative sum up to the current row).
You often need to compare a single row against an aggregate of its peers (e.g., an employee's salary vs their department's average).
Where used: analytics dashboards, comparing individual vs group metrics
Why learn this
- Compute running totals and moving averages without complex self-joins.
- Easily compare an individual row to a group aggregate (like percent of total).
Query walkthrough
SELECT name, department, salary, AVG(salary) OVER (PARTITION BY department) AS dept_avg FROM employees WHERE department IN ('Engineering', 'Sales') ORDER BY department, name;Focus: Watch how the AVG(salary) is calculated per department without collapsing the individual employee rows.
Common mistakes
- Forgetting the OVER clause: If you use an aggregate function like SUM() without an OVER clause, SQL expects a GROUP BY and will fail if you also select unaggregated columns.
Glossary
- aggregate-like
- Behaving similarly to functions that summarize data, but applied in a way that keeps individual row details.
- subgroups
- Smaller, distinct sets of rows created within a larger partition of data.
- peers
- Rows that share the same characteristics or belong to the same partition, like employees in the same department.
Recall questions
- What is the main difference between GROUP BY and PARTITION BY?
Understanding checks
What rows does this query return?
Wendy | 130000 | 130000 Xavier | 95000 | 130000 Yara | 90000 | 130000
The empty OVER () clause treats the entire result set (Sales employees) as a single window, assigning the maximum salary (130000) to every row.
What rows does this query return?
Sara | Executive | 1 Tom | Engineering | 3 Uma | Engineering | 3 Victor | Engineering | 3 Wendy | Sales | 1
First, the WHERE clause filters out salaries <= 100000. Then, PARTITION BY department counts the remaining employees per department and assigns that count to each row.
Practice tasks
Add a partition to count orders per customer
Given this query, add a window function to count the total number of orders for each customer alongside the order details.
Challenge
Compare product price to category maximum
Write a query returning all products, their category, price, and the maximum price in that category using a window function. Order by category and then by price descending.
Continue learning
Previous: GROUP BY
Previous: ORDER BY, LIMIT & OFFSET
Next: ROW_NUMBER / RANK / DENSE_RANK
Next: LEAD & LAG