Window Aggregation with Multiple Partitions
What You'll Learn
You'll learn how to apply different window aggregation functions with varying PARTITION BY groupings in a single query to answer multiple analytical questions at once.
The Core Idea
Sometimes you need to calculate aggregates across different groupings simultaneously. For example, you might want both:
- A running total by department
- An overall company-wide average by year
- A moving average by region and product category
Rather than writing separate queries or complex joins, you can define multiple window functions, each with its own PARTITION BY clause, all in the same SELECT statement.
Think of it like viewing your data through multiple lenses at once—each window function creates its own "grouping context" without affecting the others.
Practical Example
SELECT
employee_name,
department,
region,
salary,
-- Department-level aggregation
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
-- Region-level aggregation
SUM(salary) OVER (PARTITION BY region ORDER BY hire_date) AS region_running_total,
-- Overall aggregation (no partition = entire dataset)
MAX(salary) OVER () AS company_max_salary
FROM employees;
Each OVER clause operates independently:
- The first partitions by
department(compares salary to department average) - The second partitions by
regionwith ordering (shows regional cumulative costs) - The third has no partition (applies across all rows)
This gives you rich, multi-dimensional context for each row without multiple queries or self-joins.
When to Use This
Use multiple partitions when you need to compare a value against different aggregation levels simultaneously—like showing an employee's salary relative to their department, their region, and the company overall.
Key Takeaway: You can use multiple window functions with different PARTITION BY groupings in a single query, letting each function create its own independent analytical context for comprehensive row-level insights.