Named windows with WINDOW clause in PostgreSQL - Step-by-Step Execution

Concept Flow - Named windows with WINDOW clause

Define Named Window

↓

Use Named Window in Query

↓

Apply Window Function with Named Window

↓

Return Result with Window Function Applied

First, you define a named window using the WINDOW clause. Then, you use that named window in window functions within your query to apply the same window specification easily.

Execution Sample

PostgreSQL

SELECT department, employee, salary,
       RANK() OVER dept_window AS rank_in_dept
FROM employees
WINDOW dept_window AS (PARTITION BY department ORDER BY salary DESC);

This query ranks employees within each department by salary using a named window called dept_window.

Execution Table

Step	Action	Window Definition	Window Function Applied	Output Row Example
1	Parse WINDOW clause	dept_window = PARTITION BY department ORDER BY salary DESC	None yet	No output yet
2	Start processing rows	dept_window defined	RANK() OVER dept_window	First employee in 'Sales' department with highest salary gets rank 1
3	Apply RANK() to next employee in 'Sales'	dept_window used	RANK() OVER dept_window	Second highest salary in 'Sales' gets rank 2
4	Apply RANK() to employee in 'HR'	dept_window used	RANK() OVER dept_window	Highest salary in 'HR' gets rank 1
5	Continue for all employees	dept_window used	RANK() OVER dept_window	Ranks assigned per department
6	Return final result set	dept_window used	RANK() OVER dept_window	All employees with ranks per department

💡 All rows processed with RANK() applied using the named window dept_window

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current Row	None	Employee A (Sales, 90000)	Employee B (Sales, 85000)	Employee C (HR, 95000)	Last employee processed
dept_window	Undefined	PARTITION BY department ORDER BY salary DESC	Same	Same	Same
RANK() Result	None	1	2	1	Ranks assigned per row

Key Moments - 2 Insights

Why do we define a named window instead of writing the PARTITION BY and ORDER BY inside each window function?

Defining a named window lets you reuse the same window specification multiple times in the query, avoiding repetition and making the query easier to read and maintain, as shown in the execution_table steps 1 and 2.

Does the named window itself produce any output rows?

No, the named window is just a definition of how to partition and order rows. The window functions like RANK() use this definition to compute results, as seen in steps 2 to 5.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What rank does the second employee in the 'Sales' department receive?

A 1

B 2

C 3

D None

💡 Hint

Check the 'Output Row Example' column at step 3 in the execution_table.

At which step is the named window first used by a window function?

A Step 4

B Step 1

C Step 2

D Step 6

💡 Hint

Look at the 'Window Function Applied' column to see when RANK() OVER dept_window is first applied.

If we remove the WINDOW clause and write PARTITION BY and ORDER BY inside each RANK() call, how does the execution_table change?

A The steps remain the same but the window definition is repeated each time.

B The window definition disappears from the table.

C The RANK() function cannot be applied.

D The output rows change.

💡 Hint

Consider how named windows help reuse window definitions as explained in key_moments.

Concept Snapshot

Named windows let you define a window once using WINDOW clause.
Use the named window in window functions like RANK() OVER window_name.
This avoids repeating PARTITION BY and ORDER BY.
Improves query readability and maintenance.
Named windows do not produce output themselves.
Window functions apply the named window to compute results.

Full Transcript

Named windows with the WINDOW clause allow you to define a window specification once and reuse it in multiple window functions within a query. The flow starts by defining the named window with PARTITION BY and ORDER BY clauses. Then, window functions like RANK() use this named window to calculate results per partition. The execution table shows parsing the window clause first, then applying the window function row by row, assigning ranks within each department. Variables track the current row, the named window definition, and the rank result. Key moments clarify why named windows are useful and that they do not produce output themselves. The visual quiz tests understanding of when the named window is used and the effect of removing the WINDOW clause. The snapshot summarizes the syntax and benefits of named windows.

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