Window frame specification (ROWS BETWEEN) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW

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

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

SQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales VALUES (1, 100), (2, 200), (3, 300);

A [{"id":1,"amount":100,"running_total":300},{"id":2,"amount":200,"running_total":500},{"id":3,"amount":300,"running_total":600}]

B [{"id":1,"amount":100,"running_total":600},{"id":2,"amount":200,"running_total":600},{"id":3,"amount":300,"running_total":600}]

C [{"id":1,"amount":100,"running_total":100},{"id":2,"amount":200,"running_total":300},{"id":3,"amount":300,"running_total":600}]

D [{"id":1,"amount":100,"running_total":100},{"id":2,"amount":200,"running_total":200},{"id":3,"amount":300,"running_total":300}]

💡 Hint

Think about how the window frame includes all rows from the start up to the current row.

The frame includes all rows from the first row (UNBOUNDED PRECEDING) up to the current row, so the running total accumulates amounts as we move down the rows.

❓ query_result

intermediate

Effect of ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

What is the output of this query on the sales table?

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

SQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales VALUES (1, 100), (2, 200), (3, 300);

A [{"id":1,"amount":100,"avg_last_two":200.0},{"id":2,"amount":200,"avg_last_two":250.0},{"id":3,"amount":300,"avg_last_two":300.0}]

B [{"id":1,"amount":100,"avg_last_two":100.0},{"id":2,"amount":200,"avg_last_two":150.0},{"id":3,"amount":300,"avg_last_two":250.0}]

C [{"id":1,"amount":100,"avg_last_two":100.0},{"id":2,"amount":200,"avg_last_two":200.0},{"id":3,"amount":300,"avg_last_two":300.0}]

D [{"id":1,"amount":100,"avg_last_two":150.0},{"id":2,"amount":200,"avg_last_two":250.0},{"id":3,"amount":300,"avg_last_two":350.0}]

💡 Hint

The frame includes the current row and one row before it, except for the first row which has only itself.

For the first row, only one row is in the frame, so average is 100. For the second row, rows 1 and 2 are included, average (100+200)/2=150. For the third row, rows 2 and 3 are included, average (200+300)/2=250.

📝 Syntax

advanced

Identify the syntax error in window frame clause

Which option contains a syntax error in the window frame specification?

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

A ROWS BETWEEN CURRENT ROW AND 1 PRECEDING

B ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

C ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

D ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

💡 Hint

The frame must specify a start point that comes before the end point.

The clause 'ROWS BETWEEN CURRENT ROW AND 1 PRECEDING' is invalid because the frame start (CURRENT ROW) is after the frame end (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 for calculating a cumulative sum ordered by id in a large table?

SELECT id, amount, SUM(amount) OVER (ORDER BY id ROWS BETWEEN ? AND CURRENT ROW) FROM sales;

A UNBOUNDED PRECEDING

B 1 PRECEDING

C UNBOUNDED FOLLOWING

D CURRENT ROW

💡 Hint

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

Using 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW' includes all rows from the start to current, which is the correct and efficient frame for cumulative sums.

🧠 Conceptual

expert

Understanding frame exclusion with ROWS BETWEEN

Consider the query:

SELECT id, amount, SUM(amount) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS sum_excluded_current FROM sales ORDER BY id;

What does this window frame exclude from the sum calculation?

A The current row and the immediately preceding row

B Only the current row

C The current row and the two preceding rows

D The current row and all following rows

💡 Hint

The frame includes rows from 2 before to 1 before the current row, excluding the current row itself.

The frame sums rows starting two before the current row up to one before it, so the current row is excluded along with any rows after it.