Named windows with WINDOW clause in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a named window called w1 that orders rows by the column 'date'.

PostgreSQL

SELECT *, ROW_NUMBER() OVER w1 FROM sales [1];

Drag options to blanks, or click blank then click option'

AWINDOW w1 AS (ORDER BY date)

BWINDOW w1 PARTITION BY date

CORDER BY w1

DPARTITION BY w1

Attempts:

3 left

💡 Hint

Common Mistakes

Using PARTITION BY instead of ORDER BY in the WINDOW clause.

Trying to use WINDOW clause inside OVER without AS keyword.

The WINDOW clause defines a named window 'w1' with ordering by 'date'. This allows reuse of the window specification.

2fill in blank

medium

Complete the code to use the named window 'w1' in the ROW_NUMBER() function.

PostgreSQL

SELECT *, ROW_NUMBER() OVER [1] FROM sales WINDOW w1 AS (ORDER BY date);

Drag options to blanks, or click blank then click option'

A(PARTITION BY w1)

B(WINDOW w1)

C(ORDER BY w1)

D(w1)

Attempts:

3 left

💡 Hint

Common Mistakes

Adding PARTITION BY or WINDOW keywords inside OVER() when using a named window.

Using ORDER BY inside OVER() when the window is already defined.

To use a named window, just put its name inside the OVER() clause without extra keywords.

3fill in blank

hard

Fix the error in the WINDOW clause that tries to define a window named w2 partitioned by 'region' and ordered by 'sales'.

PostgreSQL

SELECT *, SUM(sales) OVER w2 FROM sales WINDOW w2 AS [1];

Drag options to blanks, or click blank then click option'

A(ORDER BY region PARTITION BY sales)

B(PARTITION region BY sales)

C(PARTITION BY region ORDER BY sales)

D(PARTITION BY sales ORDER BY region)

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the BY keyword after PARTITION.

Swapping the order of PARTITION BY and ORDER BY clauses.

The correct syntax for partitioning is PARTITION BY column_name. The original code missed the BY keyword.

4fill in blank

hard

Fill both blanks to define two named windows: w1 partitions by 'category', w2 orders by 'date'.

PostgreSQL

SELECT *, SUM(amount) OVER w1, ROW_NUMBER() OVER w2 FROM transactions WINDOW w1 AS ([1]), w2 AS ([2]);

Drag options to blanks, or click blank then click option'

APARTITION BY category

BORDER BY category

CORDER BY date

DPARTITION BY date

Attempts:

3 left

💡 Hint

Common Mistakes

Using ORDER BY in w1 instead of PARTITION BY.

Using PARTITION BY in w2 instead of ORDER BY.

w1 partitions rows by 'category', so it uses PARTITION BY category. w2 orders rows by 'date', so it uses ORDER BY date.

5fill in blank

hard

Fill all three blanks to define a named window w3 that partitions by 'department', orders by 'salary', and uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

PostgreSQL

SELECT *, SUM(salary) OVER w3 FROM employees WINDOW w3 AS ([1] [2] [3]);

Drag options to blanks, or click blank then click option'

APARTITION BY department

BORDER BY salary

CROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

DRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE instead of ROWS in the frame clause.

Omitting PARTITION BY or ORDER BY clauses.

Placing frame clause before ORDER BY.

The window w3 partitions by 'department', orders by 'salary', and defines the frame as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for cumulative sum.

Practice

1. What is the main purpose of using the WINDOW clause in PostgreSQL?

easy

A. To join multiple tables using window functions

B. To create temporary tables for complex queries

C. To define named window specifications for reuse in the query

D. To specify the order of rows in a SELECT statement

Solution

1. Step 1: Understand the role of WINDOW clause

   The WINDOW clause allows defining named window specifications once for reuse.

2. Step 2: Compare options with WINDOW clause purpose

   Options A, B, and C describe other SQL features, not the WINDOW clause.

3. Final Answer:

   To define named window specifications for reuse in the query -> Option C

4. Quick Check:

   WINDOW clause = named window reuse [OK]

Hint: WINDOW clause defines reusable window specs [OK]

Common Mistakes:

• Confusing WINDOW with temporary tables
• Thinking WINDOW orders rows directly
• Mixing WINDOW with JOIN operations

2. Which of the following is the correct syntax to define a named window called w1 that orders rows by date ascending?

easy

A. WINDOW w1 (ORDER BY date ASC)

B. WINDOW w1 AS (ORDER BY date ASC)

C. WINDOW w1 BY (ORDER date ASC)

D. WINDOW AS w1 ORDER BY date ASC

Solution

1. Step 1: Recall WINDOW clause syntax

   The correct syntax is WINDOW name AS (window_definition), e.g., WINDOW w1 AS (ORDER BY date ASC).

2. Step 2: Check each option's syntax

   WINDOW w1 AS (ORDER BY date ASC) matches correct syntax. Options A, B, and D have incorrect keyword order or missing AS.

3. Final Answer:

   WINDOW w1 AS (ORDER BY date ASC) -> Option B

4. Quick Check:

   WINDOW name AS (definition) syntax [OK]

Hint: Use WINDOW name AS (definition) syntax [OK]

Common Mistakes:

• Omitting AS keyword
• Using BY instead of ORDER BY
• Misplacing parentheses

3. Given the query:

SELECT department, employee, salary, AVG(salary) OVER w AS avg_salary
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY employee)
ORDER BY department, employee;

What does the avg_salary column show?

medium

A. The average salary of employees in the same department up to the current employee

B. The average salary of all employees in the company

C. The average salary of employees partitioned by department without order

D. The average salary of employees ordered by employee name across all departments

Solution

1. Step 1: Analyze the WINDOW clause

   The window named w partitions rows by department and orders by employee.

2. Step 2: Understand AVG() OVER w behavior

   AVG(salary) OVER w calculates the average salary for rows in the same department up to the current employee in order.

3. Final Answer:

   The average salary of employees in the same department up to the current employee -> Option A

4. Quick Check:

   Partition + order = running average per department [OK]

Hint: Partition + order means running calculation per group [OK]

Common Mistakes:

• Ignoring ORDER BY in window
• Assuming average over entire table
• Confusing partition with no partition

4. Consider this query:

SELECT name, salary, SUM(salary) OVER w
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY name BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

It returns an error. What is the likely cause?

medium

A. The window frame clause requires ROWS or RANGE keyword explicitly

B. The window frame clause is invalid without RANGE keyword

C. The WINDOW clause must come after ORDER BY clause

D. The window name cannot be used in the OVER clause

Solution

1. Step 1: Check window frame syntax

   The frame clause must specify ROWS or RANGE explicitly before frame boundaries.

2. Step 2: Identify error cause

   The query has ORDER BY name BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, missing the required ROWS or RANGE keyword before BETWEEN.

3. Final Answer:

   The window frame clause requires ROWS or RANGE keyword explicitly -> Option A

4. Quick Check:

   Window frame needs ROWS or RANGE keyword [OK]

Hint: Always specify ROWS or RANGE in window frame [OK]

Common Mistakes:

• Placing WINDOW clause after ORDER BY
• Omitting ROWS or RANGE keyword
• Using window name incorrectly in OVER

5. You want to calculate the cumulative sum of sales per region ordered by date, and reuse this window in multiple queries. Which is the best way to define and use a named window for this purpose?

hard

A. Define WINDOW sales_win AS (PARTITION BY region ORDER BY date ROWS UNBOUNDED PRECEDING) and use SUM(sales) OVER sales_win

B. Use SUM(sales) OVER (PARTITION BY region ORDER BY date) without WINDOW clause

C. Define WINDOW sales_win AS (ORDER BY date PARTITION BY region) and use SUM(sales) OVER sales_win

D. Define WINDOW sales_win AS (PARTITION BY region ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and use SUM(sales) OVER sales_win

Solution

1. Step 1: Understand cumulative sum window frame

   Cumulative sum requires a frame from the start to current row, so use RANGE or ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

2. Step 2: Check window definition correctness

   Define WINDOW sales_win AS (PARTITION BY region ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and use SUM(sales) OVER sales_win correctly defines WINDOW with PARTITION BY region, ORDER BY date, and RANGE frame for cumulative sum.

3. Step 3: Compare with other options

   Define WINDOW sales_win AS (PARTITION BY region ORDER BY date ROWS UNBOUNDED PRECEDING) and use SUM(sales) OVER sales_win uses ROWS UNBOUNDED PRECEDING which sums the entire partition, not cumulatively. Define WINDOW sales_win AS (ORDER BY date PARTITION BY region) and use SUM(sales) OVER sales_win has wrong order of clauses. Use SUM(sales) OVER (PARTITION BY region ORDER BY date) without WINDOW clause does not reuse named window.

4. Final Answer:

   Define WINDOW sales_win AS (PARTITION BY region ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and use SUM(sales) OVER sales_win -> Option D

5. Quick Check:

   Named window with correct frame = Define WINDOW sales_win AS (PARTITION BY region ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) and use SUM(sales) OVER sales_win [OK]

Hint: Use RANGE UNBOUNDED PRECEDING for cumulative sums [OK]

Common Mistakes:

• Omitting frame clause for cumulative sum
• Incorrect order of PARTITION BY and ORDER BY
• Not using named window for reuse