Calculating Weighted Averages in Windows

In this lesson, you will learn how to calculate a weighted average across a specific window of data by combining standard window functions with basic arithmetic.

The Concept

A simple average treats every row equally. However, in many analytical scenarios—such as financial analysis or sensor data smoothing—some observations are more significant than others. To calculate a weighted average, we must multiply each value by its corresponding weight and then divide the sum of those products by the sum of the weights.

When working with window functions, we cannot simply use a built-in AVG() function because it lacks a "weight" parameter. Instead, we use the property that SUM(value * weight) / SUM(weight) allows us to achieve the same result over a sliding window.

Practical Application

Imagine you are tracking the performance of an investment portfolio where each asset has a price and a share_count (the weight). You want to see the "weighted average price" over the last three entries to smooth out volatility.

You can perform this calculation using window frames:

SELECT 
    date,
    price,
    share_count,
    SUM(price * share_count) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
    / 
    NULLIF(SUM(share_count) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) 
    AS weighted_moving_avg
FROM portfolio_data;

Why This Works

1. The Numerator: SUM(price * share_count) calculates the total value of the window, accounting for the weight of each individual row.
2. The Denominator: SUM(share_count) calculates the total weight of the window.
3. NULLIF Protection: We wrap the denominator in NULLIF(..., 0) to prevent a "division by zero" error if a window happens to contain no data or zero shares, which would otherwise crash your query.

By partitioning the window dynamically, you can apply this logic to specific categories or time intervals, providing a sophisticated view of your data that a standard AVG() function simply cannot provide.

Key Takeaway: To calculate a weighted average in a window, divide the windowed sum of (value × weight) by the windowed sum of the weights.