Challenge - 5 Problems
Window Function Mastery
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate2:00remaining
Output of SUM() as a window function
Given the table sales with columns
id, region, and amount, what is the output of this query?SELECT id, region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY id) AS running_total FROM sales ORDER BY id;
PostgreSQL
CREATE TABLE sales (id INT, region TEXT, amount INT); INSERT INTO sales VALUES (1, 'East', 100), (2, 'East', 150), (3, 'West', 200), (4, 'East', 50), (5, 'West', 100);
Attempts:
2 left
💡 Hint
Think about how the SUM() window function adds amounts within each region, ordered by id.
✗ Incorrect
The SUM() OVER (PARTITION BY region ORDER BY id) calculates a running total of amounts for each region separately, adding amounts in order of id.
❓ query_result
intermediate2:00remaining
Output of AVG() as a window function with frame
Consider the table scores with columns
student_id and score. What is the output of this query?SELECT student_id, score, AVG(score) OVER (ORDER BY student_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_score FROM scores ORDER BY student_id;
PostgreSQL
CREATE TABLE scores (student_id INT, score INT); INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70), (4, 100);
Attempts:
2 left
💡 Hint
The frame includes the current row and the one before it, so average is over 1 or 2 scores.
✗ Incorrect
For student 1, only one row (80). For student 2, average of 80 and 90 = 85. For student 3, average of 90 and 70 = 80. For student 4, average of 70 and 100 = 85.
📝 Syntax
advanced2:00remaining
Identify the syntax error in COUNT() window function
Which option contains a syntax error when using COUNT() as a window function?
PostgreSQL
SELECT department, employee_id, COUNT(*) OVER PARTITION BY department FROM employees;
Attempts:
2 left
💡 Hint
Check the parentheses around the window specification.
✗ Incorrect
The window specification for OVER must be enclosed in parentheses. Option B misses these parentheses, causing a syntax error.
❓ optimization
advanced2:00remaining
Optimizing AVG() window function with partition
You want to calculate the average sales per region for each sale row in the sales table. Which query is the most efficient and correct to achieve this?
PostgreSQL
Table sales(id INT, region TEXT, amount INT)
Attempts:
2 left
💡 Hint
Think about how to get average per region for each row without grouping rows.
✗ Incorrect
Option A uses AVG() as a window function partitioned by region, giving average per region for each row. Option A groups rows and loses individual rows. Option A averages over all rows ignoring region. Option A orders but does not partition, so average is over all rows ordered.
🧠 Conceptual
expert3:00remaining
Understanding COUNT() window function behavior
Given the table orders with columns
Assuming data:
order_id, customer_id, and status, what will be the result of this query?SELECT order_id, customer_id, status, COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_count FROM orders ORDER BY order_id;
Assuming data:
order_id | customer_id | status
1 | 101 | shipped
2 | 102 | pending
3 | 101 | shipped
4 | 101 | pending
5 | 102 | shippedPostgreSQL
CREATE TABLE orders (order_id INT, customer_id INT, status TEXT); INSERT INTO orders VALUES (1, 101, 'shipped'), (2, 102, 'pending'), (3, 101, 'shipped'), (4, 101, 'pending'), (5, 102, 'shipped');
Attempts:
2 left
💡 Hint
COUNT(*) counts rows per customer_id up to current order_id in order.
✗ Incorrect
The window frame counts all rows for the same customer_id from the first order up to the current order ordered by order_id, producing a running count per customer.