MySQL Query to Calculate a Rolling 3-Month Sales Average

Calculate the Average Sales Over a Rolling 3-Month Window

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

Solution:

-- Begin the SELECT statement to define the columns to retrieve from the MonthlySales table
SELECT 
    -- Retrieve the Month column to display the corresponding month for each sales record
    Month, 
    -- Retrieve the Sales column to show the sales amount for each month
    Sales,
    -- Calculate the rolling average of sales over the current row and the two preceding rows:
    -- AVG(Sales) computes the average of sales within the defined window.
    -- The window is defined by ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, which includes the current month and the previous two months.
    AVG(Sales) OVER (ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS RollingAvg
-- Specify the MonthlySales table as the source of the data
FROM MonthlySales;

Explanation:

• Purpose of the Query:
• The goal is to calculate the average sales over a rolling 3-month period.
• This demonstrates the use of the AVG() window function with a frame clause.
• 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 rolling 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 smooth out short-term fluctuations in sales by calculating a rolling average.

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the weighted moving average of sales over a 3-month period.
• Write a MySQL query to find months where sales exceeded the rolling 3-month average.
• Write a MySQL query to compute the difference between actual sales and rolling average sales.
• Write a MySQL query to calculate the exponential moving average of sales over time.

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

Previous MySQL Exercise: Find the Top 5 Highest Sales by Region.
Next MySQL Exercise: Find the Employees with the Highest Salary in Each Department.