Window frame specification (ROWS BETWEEN) in SQL - Mini Project: Build & Apply

Using Window Frame Specification with ROWS BETWEEN in SQL

📖 Scenario: You work for a retail company that wants to analyze sales trends over time. You have a table of daily sales data and want to calculate a running total of sales for each day, considering only the current day and the two previous days.

🎯 Goal: Create a SQL query that uses a window function with a ROWS BETWEEN frame to calculate a 3-day running total of sales.

📋 What You'll Learn

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

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

Write a SQL query that uses SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to calculate the running total.

Order the results by sale_date.

💡 Why This Matters

🌍 Real World

Retail companies often analyze sales trends over time to make decisions about inventory and marketing. Running totals help understand recent sales performance.

💼 Career

Knowing how to use window functions with frame specifications is valuable for data analysts and database developers working with time series or ordered data.

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. Then insert these exact rows: ('2024-06-01', 100), ('2024-06-02', 150), ('2024-06-03', 200), ('2024-06-04', 130), and ('2024-06-05', 170).

SQL

-- Create the daily_sales table and insert the specified data
-- Your code here

Need a hint?

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

2

Add an ORDER BY clause for the window function

Define a window ordering by sale_date using ORDER BY sale_date inside the window function. This will be used to calculate running totals in date order.

SQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

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

-- Add ORDER BY sale_date inside the window function
-- Your code here

Need a hint?

Use SUM(amount) OVER (ORDER BY sale_date) to define the window ordering.

3

Add the window frame specification with ROWS BETWEEN

Modify the window function to include the frame specification ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to calculate the sum of the current row and the two previous rows.

SQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

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

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales;
-- Your code here

Need a hint?

Add ROWS BETWEEN 2 PRECEDING AND CURRENT ROW after ORDER BY sale_date inside the window function.

4

Order the final results by sale_date

Add an ORDER BY sale_date clause at the end of the query to display the results sorted by date.

SQL

CREATE TABLE daily_sales (
    sale_date DATE,
    amount INTEGER
);

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

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales
ORDER BY sale_date;
-- Your code here

Need a hint?

Add ORDER BY sale_date at the end of the query to sort the output.