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

Comparing Values to Window Aggregates

Calculate each row's difference or ratio relative to partition totals, averages, or other aggregates.

Comparing Values to Window Aggregates

What you'll learn: How to calculate each row's difference or ratio relative to partition-level aggregates like totals, averages, or other summary metrics.

The Core Idea

You've learned how to compute window aggregates like SUM(), AVG(), MIN(), and MAX() over partitions. Now comes the powerful part: comparing individual row values directly to these aggregates in the same query. This lets you answer questions like "How much did this salesperson exceed the regional average?" or "What percentage of the total does this sale represent?"

The pattern is simple: calculate your window aggregate in one expression, then reference the original row value alongside it to compute differences, ratios, or percentage contributions.

Plain-English Example

Imagine a sales team where you've already calculated the total sales for each region using SUM() OVER (PARTITION BY region). Now, for each individual sale, you want to know:

  • Absolute difference: sale_amount - regional_total (usually negative, showing how much smaller this sale is)
  • Percentage of total: sale_amount / regional_total * 100
  • Difference from average: sale_amount - AVG(sale_amount) OVER (PARTITION BY region)

You're essentially holding up each row against its group's summary statistics in real-time.

Common Calculations

  • Percentage contribution: value / SUM(value) OVER (PARTITION BY category)
  • Deviation from mean: value - AVG(value) OVER (PARTITION BY category)
  • Distance from max: MAX(value) OVER (PARTITION BY category) - value
  • Ratio to partition average: value / AVG(value) OVER (PARTITION BY category)

These calculations appear as new columns in your result set, making variance analysis and relative performance metrics immediately visible.

Key Takeaway: By combining individual row values with window aggregates in arithmetic expressions, you can instantly reveal each row's relative standing—its contribution percentage, deviation from average, or distance from extremes—all without separate subqueries.