Window frame (ROWS BETWEEN, RANGE BETWEEN) in PostgreSQL - Mini Project: Build & Apply

Window Frame with ROWS BETWEEN and RANGE BETWEEN in PostgreSQL

📖 Scenario: You work for a sales company that tracks daily sales amounts for different stores. You want to analyze sales trends by calculating running totals and moving averages over specific ranges of days.

🎯 Goal: Build a PostgreSQL query that uses window functions with ROWS BETWEEN and RANGE BETWEEN frame clauses to calculate running totals and moving averages of sales amounts ordered by date.

📋 What You'll Learn

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

Insert the exact sales data for 3 stores over 5 consecutive days.

Write a query that calculates the running total of sales per store ordered by sale_date using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Write a query that calculates the moving average of sales per store ordered by sale_date using RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW.

Use window functions with the correct partitioning and ordering.

💡 Why This Matters

🌍 Real World

Businesses often analyze sales data over time to understand trends and performance. Window functions with frame clauses help calculate running totals and moving averages efficiently.

💼 Career

Data analysts and database developers use window functions in SQL to write advanced queries for reporting and analytics.

1

Create the daily_sales table and insert data

Create a table called daily_sales with columns store_id as integer, sale_date as date, and amount as numeric. Then insert these exact rows: (1, '2024-06-01', 100), (1, '2024-06-02', 150), (1, '2024-06-03', 200), (1, '2024-06-04', 250), (1, '2024-06-05', 300), (2, '2024-06-01', 80), (2, '2024-06-02', 120), (2, '2024-06-03', 160), (2, '2024-06-04', 200), (2, '2024-06-05', 240), (3, '2024-06-01', 90), (3, '2024-06-02', 110), (3, '2024-06-03', 130), (3, '2024-06-04', 170), (3, '2024-06-05', 210).

PostgreSQL

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

Need a hint?

Use CREATE TABLE with the specified columns and INSERT INTO with the exact rows.

2

Define the window partition and order

Create a window specification called sales_window that partitions by store_id and orders by sale_date. Use this window in your queries later.

PostgreSQL

CREATE TABLE daily_sales (
    store_id INTEGER,
    sale_date DATE,
    amount NUMERIC
);

INSERT INTO daily_sales (store_id, sale_date, amount) VALUES
(1, '2024-06-01', 100),
(1, '2024-06-02', 150),
(1, '2024-06-03', 200),
(1, '2024-06-04', 250),
(1, '2024-06-05', 300),
(2, '2024-06-01', 80),
(2, '2024-06-02', 120),
(2, '2024-06-03', 160),
(2, '2024-06-04', 200),
(2, '2024-06-05', 240),
(3, '2024-06-01', 90),
(3, '2024-06-02', 110),
(3, '2024-06-03', 130),
(3, '2024-06-04', 170),
(3, '2024-06-05', 210);

-- Define the window specification sales_window
-- Your code here

Need a hint?

Use the WINDOW clause with PARTITION BY store_id and ORDER BY sale_date in a SELECT query.

3

Calculate running total using ROWS BETWEEN

Write a SELECT query on daily_sales that shows store_id, sale_date, amount, and a new column running_total. Calculate running_total as the sum of amount over the window sales_window with frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

PostgreSQL

CREATE TABLE daily_sales (
    store_id INTEGER,
    sale_date DATE,
    amount NUMERIC
);

INSERT INTO daily_sales (store_id, sale_date, amount) VALUES
(1, '2024-06-01', 100),
(1, '2024-06-02', 150),
(1, '2024-06-03', 200),
(1, '2024-06-04', 250),
(1, '2024-06-05', 300),
(2, '2024-06-01', 80),
(2, '2024-06-02', 120),
(2, '2024-06-03', 160),
(2, '2024-06-04', 200),
(2, '2024-06-05', 240),
(3, '2024-06-01', 90),
(3, '2024-06-02', 110),
(3, '2024-06-03', 130),
(3, '2024-06-04', 170),
(3, '2024-06-05', 210);

-- Select store_id, sale_date, amount, and running_total using ROWS BETWEEN
-- Your code here

Need a hint?

Use SUM(amount) OVER sales_window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to get the running total.

4

Calculate moving average using RANGE BETWEEN

Extend the previous SELECT query to add a new column moving_avg. Calculate moving_avg as the average of amount over the window sales_window with frame clause RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW.

PostgreSQL

CREATE TABLE daily_sales (
    store_id INTEGER,
    sale_date DATE,
    amount NUMERIC
);

INSERT INTO daily_sales (store_id, sale_date, amount) VALUES
(1, '2024-06-01', 100),
(1, '2024-06-02', 150),
(1, '2024-06-03', 200),
(1, '2024-06-04', 250),
(1, '2024-06-05', 300),
(2, '2024-06-01', 80),
(2, '2024-06-02', 120),
(2, '2024-06-03', 160),
(2, '2024-06-04', 200),
(2, '2024-06-05', 240),
(3, '2024-06-01', 90),
(3, '2024-06-02', 110),
(3, '2024-06-03', 130),
(3, '2024-06-04', 170),
(3, '2024-06-05', 210);

SELECT store_id, sale_date, amount,
       SUM(amount) OVER sales_window ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS running_total,
       -- Calculate moving_avg using RANGE BETWEEN
       -- Your code here
FROM daily_sales
WINDOW sales_window AS (PARTITION BY store_id ORDER BY sale_date)
ORDER BY store_id, sale_date;

Need a hint?

Use AVG(amount) OVER sales_window RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW to calculate the moving average.