Mastering Window Functions: How to Calculate a Moving Average in SQL

USE AdventureWorksDW2019
SELECT
   c.EnglishProductCategoryName AS ProductCategory,
   d.CalendarYear,
   d.CalendarQuarter,
   SUM(f.SalesAmount) AS TotalSalesAmount,
   AVG(SUM(f.SalesAmount)) OVER (
      PARTITION BY c.EnglishProductCategoryName
      ORDER BY d.CalendarYear, d.CalendarQuarter
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   ) AS MovingAvg
FROM
   dbo.FactInternetSales f
   JOIN dbo.DimDate d ON f.OrderDateKey = d.DateKey
   JOIN dbo.DimProduct p ON f.ProductKey = p.ProductKey
   JOIN dbo.DimProductSubcategory s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
   JOIN dbo.DimProductCategory c ON s.ProductCategoryKey = c.ProductCategoryKey
GROUP BY
   c.EnglishProductCategoryName,
   d.CalendarYear,
   d.CalendarQuarter

We're using the AdventureWorksDW2019 dataset supplied by Microsoft which is a fictional sales database. This SQL query is selecting data from the database the following tables: FactInternetSales, DimDate, DimProduct, DimProductSubcategory, and DimProductCategory. It is grouping the data by product category, calendar year, and calendar quarter. Additionally, it calculates the total sales amount and a moving average of sales amount.

In this blog post, we're specifically interested in the part of the query where we are calculating a moving average so let's dive into that.  Here's that part of the code again:

AVG(SUM(f.SalesAmount)) OVER (
      PARTITION BY c.EnglishProductCategoryName
      ORDER BY d.CalendarYear, d.CalendarQuarter
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   ) AS MovingAvg

This element of the SQL query is calculating a moving average of sales amount, which can be a useful metric in analysing trends in sales over time. A moving average is a calculation that averages a specified number of data points over a given time period, with the "window" of data points "moving" forward as new data becomes available.

Let's break down this SQL element step by step:

• AVG(SUM(f.SalesAmount)): This is the main calculation for the moving average. It takes the sum of the SalesAmount column from the FactInternetSales table (which is aliased as "f"), and then calculates the average of those values. The result of this calculation will be the moving average.
• OVER: This keyword is used to specify a window function, which allows us to calculate the moving average.
• PARTITION BY c.EnglishProductCategoryName: This clause divides the data into partitions based on the values in the EnglishProductCategoryName column from the DimProductCategory table. This means that the moving average will be calculated separately for each product category.
• ORDER BY d.CalendarYear, d.CalendarQuarter: This clause orders the data within each partition by the values in the CalendarYear and CalendarQuarter columns from the DimDate table. This ensures that the moving average is calculated in chronological order.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: This clause specifies the range of rows over which the moving average should be calculated. Specifically, it includes the current row and the two preceding rows. In other words, it calculates the average of the current and two previous quarters.
• AS MovingAvg: This aliases the result of the moving average calculation as "MovingAvg", which will be included in the final query results.

To summarise, this element of the SQL query calculates a moving average of sales amount by grouping the data by product category, ordering it chronologically by quarter, and calculating the average of the current and two previous quarters. This can be a powerful tool for analysing sales trends and identifying potential opportunities for growth or improvement.

❤️ Enjoyed this article?

Forward to a friend and let them know where they can subscribe (hint: it's here).