Window frame (ROWS BETWEEN, RANGE BETWEEN) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROWS BETWEEN window frame

Given the table sales with columns id, region, and amount, what is the output of the following query?

SELECT id, region, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_sum
FROM sales
ORDER BY id;

PostgreSQL

CREATE TABLE sales (id INT, region TEXT, amount INT);
INSERT INTO sales VALUES
(1, 'North', 100),
(2, 'North', 200),
(3, 'South', 150),
(4, 'North', 50),
(5, 'South', 100);

A [{"id":1,"region":"North","amount":100,"running_sum":100},{"id":2,"region":"North","amount":200,"running_sum":300},{"id":3,"region":"South","amount":150,"running_sum":150},{"id":4,"region":"North","amount":50,"running_sum":350},{"id":5,"region":"South","amount":250,"running_sum":250}]

B [{"id":1,"region":"North","amount":100,"running_sum":100},{"id":2,"region":"North","amount":200,"running_sum":300},{"id":3,"region":"South","amount":150,"running_sum":150},{"id":4,"region":"North","amount":50,"running_sum":250},{"id":5,"region":"South","amount":100,"running_sum":250}]

C [{"id":1,"region":"North","amount":100,"running_sum":100},{"id":2,"region":"North","amount":200,"running_sum":200},{"id":3,"region":"South","amount":150,"running_sum":150},{"id":4,"region":"North","amount":50,"running_sum":50},{"id":5,"region":"South","amount":100,"running_sum":100}]

D [{"id":1,"region":"North","amount":100,"running_sum":100},{"id":2,"region":"North","amount":200,"running_sum":300},{"id":3,"region":"South","amount":150,"running_sum":250},{"id":4,"region":"North","amount":50,"running_sum":250},{"id":5,"region":"South","amount":100,"running_sum":350}]

💡 Hint

Remember that ROWS BETWEEN 1 PRECEDING AND CURRENT ROW includes the current row and the one before it within the same partition.

The window frame sums the current row's amount and the previous row's amount within the same region partition, ordered by id. For example, for id=2 in 'North', it sums amounts for id=1 and id=2 (100 + 200 = 300).

❓ query_result

intermediate

Output of RANGE BETWEEN window frame

Using the same sales table, what is the output of this query?

SELECT id, region, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY amount RANGE BETWEEN 50 PRECEDING AND CURRENT ROW) AS running_sum
FROM sales
ORDER BY id;

PostgreSQL

CREATE TABLE sales (id INT, region TEXT, amount INT);
INSERT INTO sales VALUES
(1, 'North', 100),
(2, 'North', 200),
(3, 'South', 150),
(4, 'North', 50),
(5, 'South', 100);

A [{"id":1,"region":"North","amount":100,"running_sum":100},{"id":2,"region":"North","amount":200,"running_sum":200},{"id":3,"region":"South","amount":150,"running_sum":150},{"id":4,"region":"North","amount":50,"running_sum":50},{"id":5,"region":"South","amount":100,"running_sum":100}]

B [{"id":1,"region":"North","amount":100,"running_sum":300},{"id":2,"region":"North","amount":200,"running_sum":200},{"id":3,"region":"South","amount":150,"running_sum":250},{"id":4,"region":"North","amount":50,"running_sum":150},{"id":5,"region":"South","amount":100,"running_sum":100}]

C [{"id":1,"region":"North","amount":100,"running_sum":150},{"id":2,"region":"North","amount":200,"running_sum":200},{"id":3,"region":"South","amount":150,"running_sum":250},{"id":4,"region":"North","amount":50,"running_sum":50},{"id":5,"region":"South","amount":100,"running_sum":100}]

D [{"id":1,"region":"North","amount":100,"running_sum":250},{"id":2,"region":"North","amount":200,"running_sum":200},{"id":3,"region":"South","amount":150,"running_sum":250},{"id":4,"region":"North","amount":50,"running_sum":150},{"id":5,"region":"South","amount":100,"running_sum":100}]

💡 Hint

RANGE BETWEEN considers the values of the ORDER BY column, not row positions.

RANGE BETWEEN 50 PRECEDING AND CURRENT ROW sums amounts where the amount is within 50 less than or equal to the current row's amount in the same region. For example, for id=1 (amount=100), it sums amounts between 50 and 100 in 'North' (50 and 100), totaling 150.

📝 Syntax

advanced

Identify the syntax error in window frame clause

Which option contains a syntax error in the window frame clause of this query?

SELECT id, amount,
       SUM(amount) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 1 PRECEDING) AS sum_amount
FROM sales;

A ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

C ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

D ROWS BETWEEN CURRENT ROW AND 1 PRECEDING

💡 Hint

The frame boundaries must be in logical order: start before end.

The clause 'ROWS BETWEEN CURRENT ROW AND 1 PRECEDING' is invalid because the start boundary (CURRENT ROW) comes after the end boundary (1 PRECEDING). The start must be before or equal to the end.

❓ optimization

advanced

Optimizing window frame for cumulative sum

Which window frame clause is the most efficient and correct to calculate a cumulative sum of amount ordered by id over the entire table?

SELECT id, amount,
       SUM(amount) OVER (ORDER BY id ) AS cumulative_sum
FROM sales;

A ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

C ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

D RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

💡 Hint

Cumulative sum means summing all rows from the start up to the current row.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sums all rows from the first row to the current row physically, which is efficient and precise for cumulative sums. RANGE can behave unexpectedly with duplicates.

🧠 Conceptual

expert

Effect of RANGE BETWEEN with non-numeric ORDER BY

Consider this query:

SELECT id, category, amount,
       SUM(amount) OVER (PARTITION BY category ORDER BY category RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_amount
FROM sales;

What will be the effect of using RANGE BETWEEN with ORDER BY category where category is a text column?

A It will treat all rows in the partition as peers and sum all amounts, resulting in the same sum for every row in each category.

B It will raise an error because RANGE frame requires numeric or date ORDER BY columns.

C It will behave like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, summing rows up to current row.

D It will sum only the current row's amount because text ordering cannot define a range.

💡 Hint

RANGE frame treats peers with the same ORDER BY value as a group.

When ORDER BY is on a text column, RANGE treats all rows with the same category value as peers. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all peers up to current, which is all rows in that partition, so sum is the same for all rows in that category.