Course contentsShow
← Data Science
Lesson 1021 of 2,14526. Advanced SQLFree lesson

Window Aggregation with Multiple Partitions

Apply aggregation window functions across different PARTITION BY groupings simultaneously.

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 region with 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.