Named windows with WINDOW clause in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of query using named window with WINDOW clause

Consider the following table sales with columns region, month, and amount. What is the output of this query?

SELECT region, month, amount, SUM(amount) OVER w AS running_total
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY region, month;

PostgreSQL

CREATE TABLE sales (region TEXT, month INT, amount INT);
INSERT INTO sales VALUES
('North', 1, 100),
('North', 2, 150),
('South', 1, 200),
('South', 2, 100);

SELECT region, month, amount, SUM(amount) OVER w AS running_total
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY region, month;

A [{"region": "North", "month": 1, "amount": 100, "running_total": 100}, {"region": "North", "month": 2, "amount": 150, "running_total": 150}, {"region": "South", "month": 1, "amount": 200, "running_total": 200}, {"region": "South", "month": 2, "amount": 100, "running_total": 100}]

B [{"region": "North", "month": 1, "amount": 100, "running_total": 100}, {"region": "North", "month": 2, "amount": 150, "running_total": 250}, {"region": "South", "month": 1, "amount": 200, "running_total": 200}, {"region": "South", "month": 2, "amount": 100, "running_total": 300}]

C [{"region": "North", "month": 1, "amount": 100, "running_total": 250}, {"region": "North", "month": 2, "amount": 150, "running_total": 250}, {"region": "South", "month": 1, "amount": 200, "running_total": 300}, {"region": "South", "month": 2, "amount": 100, "running_total": 300}]

D [{"region": "North", "month": 1, "amount": 100, "running_total": null}, {"region": "North", "month": 2, "amount": 150, "running_total": null}, {"region": "South", "month": 1, "amount": 200, "running_total": null}, {"region": "South", "month": 2, "amount": 100, "running_total": null}]

💡 Hint

Think about how the window function sums amounts partitioned by region and ordered by month.

The named window w partitions rows by region and orders them by month. The SUM(amount) is cumulative from the first month to the current month within each region.

📝 Syntax

intermediate

Identify the syntax error in WINDOW clause usage

Which option contains a syntax error in the use of the WINDOW clause in PostgreSQL?

SELECT id, value, AVG(value) OVER w
FROM data
WINDOW w AS (PARTITION BY category ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);

A WINDOW w AS (PARTITION BY category ORDER BY id ROWS 1 PRECEDING AND CURRENT ROW)

B )WOR TNERRUC DNA GNIDECERP 1 NEEWTEB SWOR di YB REDRO yrogetac YB NOITITRAP( SA w WODNIW

C WINDOW w AS (PARTITION BY category ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

D WINDOW w AS (PARTITION BY category ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

💡 Hint

Check the syntax for the ROWS BETWEEN clause carefully.

Option A is missing the keyword BETWEEN before the frame boundaries, which is required syntax.

🧠 Conceptual

advanced

Effect of reusing named windows in multiple window functions

Given the named window w defined as PARTITION BY dept ORDER BY salary DESC, what is the effect of using SUM(salary) OVER w and RANK() OVER w in the same query?

A Both functions use the same partition and order, so the sum and rank are calculated consistently per department ordered by descending salary.

B The functions will conflict and cause a runtime error because the same window name cannot be used twice.

C The rank function ignores the named window and uses default ordering, causing inconsistent results.

D The sum function will ignore the order clause in the named window, but rank will use it.

💡 Hint

Think about how named windows help reuse the same window definition.

Named windows allow multiple window functions to share the same partitioning and ordering, ensuring consistent calculations.

🔧 Debug

advanced

Debugging incorrect results with nested named windows

Consider this query:

SELECT employee_id, department, salary,
       AVG(salary) OVER w1 AS avg_dept_salary,
       AVG(salary) OVER w2 AS avg_high_salary
FROM employees
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Why might avg_high_salary not produce the expected cumulative average of high salaries per department?

A The syntax of referencing <code>w1</code> inside <code>w2</code> is invalid and causes a syntax error.

B The query will fail because you cannot define multiple named windows in one query.

C The window <code>w2</code> ignores the partitioning from <code>w1</code> and calculates average over the entire table.

D Because <code>w2</code> references <code>w1</code> but adds an ORDER BY clause, it creates a new window that does not partition correctly, causing unexpected results.

💡 Hint

Recall how named windows can be extended by adding clauses but must keep partitioning consistent.

When a named window references another and adds ORDER BY and frame clauses, it inherits partitioning but the frame affects cumulative calculations. If misunderstood, results may seem off but syntax is valid.

❓ optimization

expert

Optimizing query performance using named windows

You have a large table transactions with columns customer_id, transaction_date, and amount. You want to calculate both the cumulative sum of amount per customer ordered by date and the rank of each transaction per customer by amount descending.

Which query uses named windows to optimize performance by avoiding repeated window definitions?

A

SELECT customer_id, transaction_date, amount,
       SUM(amount) OVER w AS cum_sum,
       RANK() OVER w AS rank_amount
FROM transactions
WINDOW w AS (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

B

SELECT customer_id, transaction_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_amount
FROM transactions;

C

SELECT customer_id, transaction_date, amount,
       SUM(amount) OVER w AS cum_sum,
       RANK() OVER w2 AS rank_amount
FROM transactions
WINDOW w AS (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       w2 AS (PARTITION BY customer_id ORDER BY amount DESC);

D

SELECT customer_id, transaction_date, amount,
       SUM(amount) OVER w AS cum_sum,
       RANK() OVER w AS rank_amount
FROM transactions
WINDOW w AS (PARTITION BY customer_id ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

💡 Hint

Think about how named windows can be reused and extended to avoid repeating complex window definitions.

Option C defines two named windows w and w2 with different orderings for different calculations, avoiding repeating window clauses inline. This improves readability and can help the planner optimize.