Moving averages with window frames in SQL - Mini Project: Build & Apply

Calculate Moving Averages with Window Frames in SQL

📖 Scenario: You work for a retail company that tracks daily sales. You want to analyze sales trends by calculating moving averages of daily sales over a 3-day window.

🎯 Goal: Create a SQL query that calculates the 3-day moving average of daily sales using window frames.

📋 What You'll Learn

Create a table called daily_sales with columns sale_date (date) and amount (integer).

Insert the exact sales data for 5 consecutive days as specified.

Define a window frame that looks at the current day and the two previous days ordered by sale_date.

Calculate the moving average of amount over this window frame and name the result moving_avg.

💡 Why This Matters

🌍 Real World

Moving averages help businesses understand trends by smoothing out daily fluctuations in sales data.

💼 Career

Data analysts and database developers often use window functions to perform advanced calculations like moving averages for reporting and decision-making.

1

Create the daily_sales table and insert data

Create a table called daily_sales with columns sale_date of type DATE and amount of type INTEGER. Insert these exact rows into daily_sales: ('2024-01-01', 100), ('2024-01-02', 150), ('2024-01-03', 200), ('2024-01-04', 130), and ('2024-01-05', 170).

SQL

-- Create the daily_sales table and insert the 5 rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add the sales data.

2

Define the window frame for the moving average

Write a SQL SELECT statement that selects sale_date and amount from daily_sales. Add a window frame clause that orders rows by sale_date and frames from 2 rows before the current row to the current row. Assign this window frame the name sales_window.

SQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200),
('2024-01-04', 130),
('2024-01-05', 170);

-- Write a SELECT statement with a window frame named sales_window
-- Your code here

Need a hint?

Use the WINDOW clause to define sales_window with the correct ordering and frame.

3

Calculate the 3-day moving average using the window frame

Modify the SELECT statement to include a new column called moving_avg that calculates the average of amount over the window frame sales_window using the AVG() function with OVER sales_window.

SQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200),
('2024-01-04', 130),
('2024-01-05', 170);

SELECT sale_date, amount,
-- Calculate moving_avg using AVG(amount) OVER sales_window
-- Your code here
FROM daily_sales
WINDOW sales_window AS (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

Need a hint?

Use AVG(amount) OVER sales_window to calculate the moving average.

4

Complete the query with ordering by sale_date

Add an ORDER BY sale_date clause at the end of the SELECT statement to display the results in date order.

SQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200),
('2024-01-04', 130),
('2024-01-05', 170);

SELECT sale_date, amount,
       AVG(amount) OVER sales_window AS moving_avg
FROM daily_sales
WINDOW sales_window AS (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Add ORDER BY sale_date here
-- Your code here

Need a hint?

Use ORDER BY sale_date at the end of the query to sort the results by date.