Challenge - 5 Problems
Window Frame Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate2:00remaining
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);
Attempts:
2 left
💡 Hint
Remember that ROWS BETWEEN 1 PRECEDING AND CURRENT ROW includes the current row and the one before it within the same partition.
✗ Incorrect
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
intermediate2:00remaining
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);
Attempts:
2 left
💡 Hint
RANGE BETWEEN considers the values of the ORDER BY column, not row positions.
✗ Incorrect
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
advanced1:30remaining
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;
Attempts:
2 left
💡 Hint
The frame boundaries must be in logical order: start before end.
✗ Incorrect
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
advanced2:00remaining
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;
Attempts:
2 left
💡 Hint
Cumulative sum means summing all rows from the start up to the current row.
✗ Incorrect
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
expert2:30remaining
Effect of RANGE BETWEEN with non-numeric ORDER BY
Consider this query:
What will be the effect of using
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?Attempts:
2 left
💡 Hint
RANGE frame treats peers with the same ORDER BY value as a group.
✗ Incorrect
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.