Example 2: Moving Average

This SQL query calculates a moving average of sales amounts over a window of the current row and the four preceding rows. It returns the sales date, the sales amount for that date, and the moving average of sales amounts within this window.

SELECT sales_date, 
       sales_amount, 
       AVG(sales_amount) OVER (ORDER BY sales_date 
                               ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales
ORDER BY sales_date;

Query Breakdown:

1. SELECT Clause:
   • sales_date: Retrieves the date of the sales transaction.
   • sales_amount: Retrieves the amount of sales for that specific date.
   • AVG(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg: This is the window function calculating the moving average.
2. Window Function Explanation:
   • AVG(sales_amount): This is the window function used to calculate the average of the sales amounts.
   • OVER: Defines the window of rows for the function.
     • ORDER BY sales_date: Orders the rows by sales_date, which is necessary for the moving average calculation to follow the sequence of dates.
     • ROWS BETWEEN 4 PRECEDING AND CURRENT ROW: This frame clause specifies the range of rows for each calculation.
       • 4 PRECEDING: Includes the four rows before the current row.
       • CURRENT ROW: Includes the current row itself.
3. FROM Clause:
   • FROM sales: Specifies the table from which to retrieve the data.
4. ORDER BY Clause:
   • ORDER BY sales_date: Orders the final result set by sales_date to ensure that the moving averages are presented in chronological order.

Detailed Step-by-Step Execution

Initial State: Assume the sales table has the following data:

Processing Each Row:

• First Row (2024-01-01):
  • Moving Average = AVG(100) = 100 (Only 1 row available)
• Second Row (2024-01-02):
  • Moving Average = AVG(100, 150) = 125
• Third Row (2024-01-03):
  • Moving Average = AVG(100, 150, 200) = 150
• Fourth Row (2024-01-04):
  • Moving Average = AVG(100, 150, 200, 250) = 175
• Fifth Row (2024-01-05):
  • Moving Average = AVG(100, 150, 200, 250, 300) = 200
• Sixth Row (2024-01-06):
  • Moving Average = AVG(150, 200, 250, 300, 350) = 250 (First row is out of window)

Final Output: