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.