1. SQL Recipes
2. Smoothing Data
3. Calculating Weighted Moving Average

Calculating Weighted Moving Average in SQL

A weighted moving average is a moving average where the previous values within the sliding widnow are given different weights. This is usually done to make recent points more important.

Our previous example of moving average can be thought of a weighted moving average with fractional weights that add up to one. Unfortunately, we cannot build upon that example because we can't make use of window functions.

As before, we chart the quarterly revenues of Amazon for the years 2001 to 2008,

We are going to calculate a 4-period moving average. A simple way to compute the weights is to sum up the number of periods,

and create fractional weights by dividing the weights by the total.

You can pick what ever weights you want according to you needs, just make sure the weights add up to one.

Our solution will be a three step process.

1. SQL row_number to number the rows

We will first number the rows of the table,

select quarter, revenue, row_number() over () 
from amazon_revenue;

2. SQL self-join to create a sliding window

We will join the above query with itself to create a sliding window over the last three periods,

with t as 
    (select quarter, revenue, row_number() over () 
     from amazon_revenue)
select t.quarter, t.row_number as row_number, t2.quarter as quarter_2, t2.row_number as row_number_2
from t 
join t t2 on t2.row_number between t.row_number - 3 and t.row_number 

which will give you,

3. SQL case to use the fractional weights

Now it's just a matter of finding the difference between the row numbers and applying the fractional weights using a SQL CASE statement.

case 
    when t.row_number - t2.row_number = 0 then 0.4 * t2.revenue 
    when t.row_number - t2.row_number = 1 then 0.3 * t2.revenue 
    when t.row_number - t2.row_number = 2 then 0.2 * t2.revenue 
    when t.row_number - t2.row_number = 3 then 0.1 * t2.revenue 
end

We'll have to group by the quarter and sum up the weighted revenue to calculate the fully weighted average. Here's the full query:

with t as 
    (select quarter, revenue, row_number() over () 
     from amazon_revenue)
select t.quarter, avg(t.revenue) as revenue, 
sum(case 
    when t.row_number - t2.row_number = 0 then 0.4 * t2.revenue 
    when t.row_number - t2.row_number = 1 then 0.3 * t2.revenue 
    when t.row_number - t2.row_number = 2 then 0.2 * t2.revenue 
    when t.row_number - t2.row_number = 3 then 0.1 * t2.revenue 
end)
from t 
join t t2 on t2.row_number between t.row_number - 3 and t.row_number 
group by 1
order by 1