Calculating Moving Averages

JoeM · ‎12-15-2022

Introduction

This article will discuss moving averages, their benefits, drawbacks, and how to employ them in your data!

What you need to know before reading this article

Before reading this article, it's encouraged that you have some experience with SQL and working with materialized views.

Let's Go

What is a moving average?

A moving average is a mean performed on a data series over time. While there are many versions of moving averages, we are talking about simple moving averages (SMAs). This article will showcase how to calculate a 7-day moving average.

How does it work?

The assumption is that our data set carries daily entries. If your data is at a finer granularity, it's recommended to aggregate it so that each record represents a daily grain. From there, we use a SQL window function to look at the day we want to average and the six rows preceding it. For example, take the below table - the average calculated for 2022-10-7 would average the value with other values between 2022-10-01 and 2022-10-6, resulting in a value of 45.714. Once calculated, the average will move onto the next row of 2022-10-8 and calculate the same relative window.

Example

Date	Value
2022-10-01	56
2022-10-02	65
2022-10-03	23
2022-10-04	54
2022-10-05	23
2022-10-06	56
2022-10-07	43
2022-10-08	62
2022-10-09	34

How to add a moving average to your data?

See an example of the moving average sample code.

SELECT
    COVID19.Cases."date" AS "Date",
    COVID19.Cases.new_cases AS "Cases",
    COVID19.Cases."location" AS "Location",
    AVG(COVID19.Cases.new_cases) OVER (ORDER BY COVID19.Cases.date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "Seven Day Average"
FROM
    COVID19.Cases
WHERE
    (COVID19.Cases."location" IN('North America'))

What are some risks of using this technique?

Some considerations to pay attention to when using this technique are:

1) Moving averages focus solely on historical nature and are a weak technique to predict or forecast future trends.

2) Moving averages work well if the data appears to have trends embedded within it. However, moving averages for relatively flat and choppy data tends to provide weak insight.

3) Be prepared to change the length of a moving average's window to reflect your data's trends better.

4) Be aware that the first few rows of your dataset are not accurate 7-day averages! For example, if we are calculating a 7-day average on the second row of data, the average will be for two days of data.

See it in action

To see this concept in action, see the below video!