Window frame (ROWS BETWEEN, RANGE BETWEEN) in PostgreSQL

Introduction

Window frames let you control which rows are included when calculating values over a group of rows. This helps you get running totals, moving averages, and other calculations that depend on nearby rows.

When you want to calculate a running total of sales over time.

When you need to find the average score of the last 3 tests for each student.

When you want to compare each row's value to the previous or next rows in a sorted list.

When you want to calculate a cumulative sum but only within a certain range of rows.

When you want to create rankings or percentiles that consider a specific window of data.

Syntax

PostgreSQL

window_function() OVER (PARTITION BY column ORDER BY column ROWS BETWEEN frame_start AND frame_end)

window_function() OVER (PARTITION BY column ORDER BY column RANGE BETWEEN frame_start AND frame_end)

ROWS BETWEEN counts rows exactly before or after the current row.

RANGE BETWEEN considers values in the ORDER BY column and includes rows with values in a range around the current row's value.

Examples

This sums sales for the current row and the two rows before it, based on date order.

PostgreSQL

SUM(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

This calculates the average score for tests taken in the last 7 days up to the current test date for each student.

PostgreSQL

AVG(score) OVER (PARTITION BY student_id ORDER BY test_date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)

This counts all rows with salary less than or equal to the current row's salary.

PostgreSQL

COUNT(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Sample Program

This example creates a sales table and inserts 5 days of sales. It then calculates two running sums:

• running_sum_rows: sums the current and two previous rows by date.
• running_sum_range: sums amounts for sales within 2 days before the current sale date.

PostgreSQL

CREATE TABLE sales_data (
  id SERIAL PRIMARY KEY,
  sale_date DATE,
  amount INT
);

INSERT INTO sales_data (sale_date, amount) VALUES
('2024-01-01', 100),
('2024-01-02', 200),
('2024-01-03', 300),
('2024-01-04', 400),
('2024-01-05', 500);

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_sum_rows,
  SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) AS running_sum_range
FROM sales_data
ORDER BY sale_date;

Important Notes

Use ROWS when you want to count a fixed number of rows before or after the current row.

Use RANGE when you want to include rows based on the value range in the ORDER BY column, which can include multiple rows with the same value.

Be careful: RANGE requires the ORDER BY column to be numeric, date, or interval type for meaningful ranges.

Summary

Window frames control which rows are included in window function calculations.

ROWS BETWEEN counts rows by position relative to the current row.

RANGE BETWEEN counts rows by value range in the ORDER BY column.

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