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

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

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

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;

Hint

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

Practice

1. What does the ROWS BETWEEN clause specify in a window function?

easy

A. It defines the frame by counting rows relative to the current row position.

B. It defines the frame by counting rows based on the value range in the ORDER BY column.

C. It filters rows before applying the window function.

D. It sorts the rows before applying the window function.

Solution

1. Step 1: Understand window frame types

   Window frames can be defined by position (ROWS) or by value range (RANGE).

2. Step 2: Identify what ROWS BETWEEN does

   ROWS BETWEEN counts rows relative to the current row's position, not by values.

3. Final Answer:

   It defines the frame by counting rows relative to the current row position. -> Option A

4. Quick Check:

   ROWS BETWEEN = position-based frame [OK]

Hint: ROWS counts rows by position, RANGE counts by value range [OK]

Common Mistakes:

• Confusing ROWS with RANGE
• Thinking ROWS filters rows
• Assuming ROWS sorts data

2. Which of the following is the correct syntax to specify a window frame using RANGE BETWEEN from 5 preceding to current row?

easy

A. RANGE BETWEEN 5 PRECEDING AND CURRENT ROW

B. RANGE BETWEEN CURRENT ROW AND 5 PRECEDING

C. ROWS BETWEEN 5 PRECEDING AND CURRENT ROW

D. ROWS BETWEEN CURRENT ROW AND 5 PRECEDING

Solution

1. Step 1: Recall RANGE BETWEEN syntax

   The syntax is: RANGE BETWEEN <start> AND <end>, where start must be before end logically.

2. Step 2: Check order of frame boundaries

   5 PRECEDING comes before CURRENT ROW, so 'RANGE BETWEEN 5 PRECEDING AND CURRENT ROW' is correct.

3. Final Answer:

   RANGE BETWEEN 5 PRECEDING AND CURRENT ROW -> Option A

4. Quick Check:

   Correct RANGE syntax = RANGE BETWEEN 5 PRECEDING AND CURRENT ROW [OK]

Hint: Frame boundaries go from earlier to later, e.g., 5 PRECEDING to CURRENT ROW [OK]

Common Mistakes:

• Reversing frame boundaries order
• Using ROWS syntax with RANGE
• Omitting BETWEEN keyword

3. Given the table sales(date, amount) ordered by date, what will the following query return?

SELECT date, amount, SUM(amount) OVER (ORDER BY date RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW) AS running_sum FROM sales;

medium

A. Sum of amounts for the current row and 1 row before

B. Sum of all amounts up to the current row

C. Sum of amounts for the current date and the previous day

D. Sum of amounts for the current date only

Solution

1. Step 1: Understand RANGE BETWEEN with INTERVAL

   RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW includes rows with date values within 1 day before current row's date.

2. Step 2: Interpret the SUM window function

   SUM(amount) will add amounts for rows dated from 1 day before up to current date.

3. Final Answer:

   Sum of amounts for the current date and the previous day -> Option C

4. Quick Check:

   RANGE with INTERVAL sums by date range [OK]

Hint: RANGE with INTERVAL sums values in date/time range [OK]

Common Mistakes:

• Confusing RANGE with ROWS counting
• Assuming ROWS behavior for RANGE
• Ignoring INTERVAL meaning

4. Identify the error in this window frame clause:

SUM(amount) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 3 PRECEDING)

medium

A. The clause is syntactically correct.

B. ROWS cannot be used with ORDER BY date.

C. SUM cannot be used as a window function.

D. The frame boundaries are reversed; '3 PRECEDING' should come before 'CURRENT ROW'.

Solution

1. Step 1: Check frame boundary order

   Frame boundaries must go from earlier to later, so '3 PRECEDING' should be before 'CURRENT ROW'.

2. Step 2: Validate syntax correctness

   Using ROWS with ORDER BY date and SUM is valid, so no other syntax errors.

3. Final Answer:

   The frame boundaries are reversed; '3 PRECEDING' should come before 'CURRENT ROW'. -> Option D

4. Quick Check:

   Frame boundaries order matters [OK]

Hint: Frame start must be before frame end [OK]

Common Mistakes:

• Reversing frame boundaries
• Thinking ROWS can't use ORDER BY
• Misunderstanding window function usage

5. You want to calculate a moving average of sales amounts over the last 7 days including the current day, but your sales table has multiple sales per day. Which window frame clause correctly computes this using RANGE?

hard

A. RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

B. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW

C. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

D. ROWS BETWEEN 7 PRECEDING AND CURRENT ROW

Solution

1. Step 1: Understand the requirement

   We want a 7-day moving average including current day, so the frame covers 6 days before plus current day (total 7 days).

2. Step 2: Choose correct frame type

   Since multiple sales per day exist, using RANGE with INTERVAL ensures all rows within date range are included.

3. Step 3: Select correct interval

   INTERVAL '6 days' PRECEDING to CURRENT ROW covers 7 days total (6 before + current).

4. Final Answer:

   RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW -> Option B

5. Quick Check:

   Use RANGE with 6 days preceding for 7-day window [OK]

Hint: RANGE with INTERVAL covers value ranges, perfect for dates [OK]

Common Mistakes:

• Using ROWS which counts rows, not days
• Choosing wrong interval length
• Confusing 6 days preceding with 7 days