MySQL Query for 3-Month Moving Average of Sales

Calculate Moving Average of Sales

A 3-month moving average is the average of sales or data values over a rolling window of the most recent three months. It smooths out short-term fluctuations and highlights trends by recalculating the average as new data becomes available, shifting the window forward each month.

Write a MySQL query to calculate the 3-month moving average of sales using a window function.

Solution:

-- Begin the SELECT statement to define the columns to be retrieved
SELECT 
    -- Retrieve the Month column to display the month for each record
    Month, 
    -- Retrieve the Sales column to show the sales figures for each month
    Sales,
    -- Calculate the moving average of Sales using a window function
    -- The window orders the rows by Month and includes the current row plus the two preceding rows in the calculation
    AVG(Sales) OVER (ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
-- Specify the table that contains the monthly sales data
FROM MonthlySales; 

Explanation:

• Purpose of the Query:
• The goal is to calculate the moving average of sales over a 3-month period.
• This demonstrates the use of the AVG() window function with a frame clause to compute a moving average.
• Key Components:
• AVG(Sales) OVER (ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW): Calculates the average of the current row and the two preceding rows.
• SELECT Month, Sales: Retrieves the month and sales data along with the moving average.
• Why use Window Functions?:
• Window functions allow you to perform calculations over a sliding window of rows, which is useful for time-series analysis.
• Real-World Application:
• For example, in a retail business, you might want to analyze the trend of sales by calculating the moving average to smooth out short-term fluctuations.

For more Practice: Solve these Related Problems:

• Write a MySQL query to compute a 6-month moving average of sales.
• Write a MySQL query to calculate a moving average, but exclude the highest and lowest sales in each period.
• Write a MySQL query to compute the moving median of sales over a 3-month window.
• Write a MySQL query to find the moving average of sales while resetting for each product category.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Partition Employees by Department and Rank by Salary.
Next MySQL Exercise: Find the difference between Current and Previous Salary.