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.

Practice

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

easy

A. The range of rows to include relative to the current row for calculation

B. The order in which rows are sorted before aggregation

C. The columns to group by in the query

D. The filter condition for rows in the table

Solution

1. Step 1: Understand window function frame

   The window frame defines which rows are considered for each calculation in a window function.

2. Step 2: Role of ROWS BETWEEN

   The ROWS BETWEEN clause specifies the exact range of rows relative to the current row to include in the calculation.

3. Final Answer:

   The range of rows to include relative to the current row for calculation -> Option A

4. Quick Check:

   ROWS BETWEEN controls row range = D [OK]

Hint: ROWS BETWEEN defines which rows to include around current row [OK]

Common Mistakes:

• Confusing ROWS BETWEEN with ORDER BY
• Thinking it filters rows from the table
• Mixing it up with GROUP BY

2. Which of the following is the correct syntax to specify a window frame from 2 rows before to current row using ROWS BETWEEN?

easy

A. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

B. ROWS BETWEEN CURRENT ROW AND 2 PRECEDING

C. ROWS BETWEEN 2 FOLLOWING AND CURRENT ROW

D. ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

Solution

1. Step 1: Understand frame boundaries order

   The syntax is ROWS BETWEEN X AND Y where X is the start and Y is the end of the frame.

2. Step 2: Identify correct order for 2 rows before to current

   Since 2 rows before is the start and current row is the end, it must be ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

3. Final Answer:

   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -> Option A

4. Quick Check:

   Start to end order matters = A [OK]

Hint: Start boundary comes first, then end boundary in ROWS BETWEEN [OK]

Common Mistakes:

• Reversing the order of boundaries
• Using FOLLOWING instead of PRECEDING incorrectly
• Omitting CURRENT ROW keyword

3. Given the table sales(date, amount) ordered by date, what is the output of this query?

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

Assuming data:
date | amount
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 300

medium

A. [{"date": "2024-01-01", "amount": 100, "running_sum": 300}, {"date": "2024-01-02", "amount": 200, "running_sum": 500}, {"date": "2024-01-03", "amount": 300, "running_sum": 600}]

B. [{"date": "2024-01-01", "amount": 100, "running_sum": 100}, {"date": "2024-01-02", "amount": 200, "running_sum": 200}, {"date": "2024-01-03", "amount": 300, "running_sum": 300}]

C. [{"date": "2024-01-01", "amount": 100, "running_sum": 100}, {"date": "2024-01-02", "amount": 200, "running_sum": 300}, {"date": "2024-01-03", "amount": 300, "running_sum": 500}]

D. [{"date": "2024-01-01", "amount": 100, "running_sum": null}, {"date": "2024-01-02", "amount": 200, "running_sum": 300}, {"date": "2024-01-03", "amount": 300, "running_sum": 500}]

Solution

1. Step 1: Understand frame range

   The frame includes the current row and 1 row before it, so for each row sum amounts of current and previous row.

2. Step 2: Calculate running sums

   Row 1: only 100 (no previous row), Row 2: 100+200=300, Row 3: 200+300=500.

3. Final Answer:

   [{"date": "2024-01-01", "amount": 100, "running_sum": 100}, {"date": "2024-01-02", "amount": 200, "running_sum": 300}, {"date": "2024-01-03", "amount": 300, "running_sum": 500}] -> Option C

4. Quick Check:

   Sum current + 1 preceding = A [OK]

Hint: Sum includes current and 1 previous row amounts [OK]

Common Mistakes:

• Including 2 rows before instead of 1
• Ignoring the order of rows
• Assuming NULL for first row sum

4. Identify the error in this SQL window function:

SELECT id, value, AVG(value) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS avg_val FROM data;

medium

A. ORDER BY cannot be used with ROWS BETWEEN

B. The frame boundaries are reversed; start must be before end

C. Missing PARTITION BY clause

D. AVG cannot be used as a window function

Solution

1. Step 1: Check frame boundaries order

   The frame must start with the earlier row and end with the later row. Here, CURRENT ROW is after 2 PRECEDING, so order is reversed.

2. Step 2: Identify correct syntax

   The correct syntax is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, not the other way around.

3. Final Answer:

   The frame boundaries are reversed; start must be before end -> Option B

4. Quick Check:

   Frame start must be before end = C [OK]

Hint: Start boundary must be earlier than end boundary [OK]

Common Mistakes:

• Swapping start and end boundaries
• Assuming PARTITION BY is mandatory
• Thinking ORDER BY disallows ROWS BETWEEN

5. You want to calculate a moving average of the last 3 sales amounts including the current row, but your data has gaps in dates. Which window frame specification ensures exactly 3 rows are included regardless of date gaps?

hard

A. RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW

B. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

C. ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

D. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Solution

1. Step 1: Understand difference between ROWS and RANGE

   ROWS counts physical rows, while RANGE counts logical values like dates, which can skip rows if dates have gaps.

2. Step 2: Choose frame to include 3 rows

   Using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW always includes exactly 3 rows regardless of date gaps.

3. Final Answer:

   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -> Option D

4. Quick Check:

   ROWS counts rows, RANGE counts values = B [OK]

Hint: Use ROWS to count exact rows, not RANGE for gaps [OK]

Common Mistakes:

• Using RANGE which depends on value gaps
• Confusing FOLLOWING with PRECEDING
• Assuming RANGE counts rows