Why window functions are powerful in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROW_NUMBER() window function

Given a table employees with columns id, department, and salary, what is the output of this query?

SELECT id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

PostgreSQL

CREATE TABLE employees (id INT, department TEXT, salary INT);
INSERT INTO employees VALUES
(1, 'Sales', 5000),
(2, 'Sales', 7000),
(3, 'HR', 4000),
(4, 'HR', 4500),
(5, 'IT', 6000);

A [{id:2, department:'Sales', salary:7000, rank:1}, {id:1, department:'Sales', salary:5000, rank:2}, {id:4, department:'HR', salary:4500, rank:1}, {id:3, department:'HR', salary:4000, rank:2}, {id:5, department:'IT', salary:6000, rank:1}]

B [{id:1, department:'Sales', salary:5000, rank:1}, {id:2, department:'Sales', salary:7000, rank:2}, {id:3, department:'HR', salary:4000, rank:1}, {id:4, department:'HR', salary:4500, rank:2}, {id:5, department:'IT', salary:6000, rank:1}]

C [{id:2, department:'Sales', salary:7000, rank:2}, {id:1, department:'Sales', salary:5000, rank:1}, {id:4, department:'HR', salary:4500, rank:2}, {id:3, department:'HR', salary:4000, rank:1}, {id:5, department:'IT', salary:6000, rank:1}]

D [{id:1, department:'Sales', salary:5000, rank:2}, {id:2, department:'Sales', salary:7000, rank:1}, {id:3, department:'HR', salary:4000, rank:2}, {id:4, department:'HR', salary:4500, rank:1}, {id:5, department:'IT', salary:6000, rank:1}]

💡 Hint

Remember that ROW_NUMBER() resets for each department and orders salaries descending.

ROW_NUMBER() assigns a unique rank starting at 1 within each department partition, ordered by salary descending. So highest salary in each department gets rank 1.

🧠 Conceptual

intermediate

Why use window functions instead of GROUP BY?

Which statement best explains why window functions are powerful compared to GROUP BY?

A Window functions can only be used with numeric columns, unlike GROUP BY.

B Window functions always run faster than GROUP BY queries.

C Window functions allow calculations across rows without collapsing the result into fewer rows, preserving original row details.

D Window functions replace the need for any JOIN operations.

💡 Hint

Think about whether the number of rows changes after using window functions.

Window functions compute values across rows but keep all original rows intact, unlike GROUP BY which aggregates and reduces rows.

📝 Syntax

advanced

Identify the syntax error in this window function query

What is wrong with this SQL query?

SELECT id, salary, RANK() OVER (ORDER salary DESC) AS rank FROM employees;

A The alias 'rank' is a reserved keyword and cannot be used.

B Missing the keyword BY after ORDER in the OVER clause.

C RANK() cannot be used without PARTITION BY clause.

D The table name 'employees' must be enclosed in quotes.

💡 Hint

Check the syntax of the ORDER BY clause inside the OVER() window function.

The correct syntax requires ORDER BY, not just ORDER, inside the OVER() clause.

❓ optimization

advanced

Optimizing a query with window functions

You have a large sales table and want to calculate the running total of sales per region ordered by date. Which approach is most efficient?

A Use a correlated subquery to sum sales up to the current date for each row.

B Calculate running total in application code after fetching all rows.

C Use GROUP BY region and date, then join back to the original table.

D Use SUM(sales) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

💡 Hint

Window functions can compute running totals efficiently inside the database.

Using SUM() as a window function with proper partitioning and ordering is optimized by the database engine and avoids expensive subqueries or joins.

🔧 Debug

expert

Debugging unexpected results with window functions

A query uses ROW_NUMBER() OVER (ORDER BY salary) but returns unexpected row numbering when multiple employees have the same salary. What is the cause?

A ROW_NUMBER() assigns unique numbers even if salaries are the same, so order among ties is arbitrary.

B The query must include PARTITION BY salary to fix numbering.

C ROW_NUMBER() cannot be used without a GROUP BY clause.

D The ORDER BY clause inside OVER() must include employee id to break ties.

💡 Hint

Think about how ROW_NUMBER() handles ties in ordering.

ROW_NUMBER() always assigns unique sequential numbers. When ORDER BY values tie, the order among those tied rows is arbitrary and can cause unexpected numbering.

Practice

1. What is the main advantage of using window functions in PostgreSQL?

easy

A. They only work on single rows without considering others.

B. They allow calculations across rows without collapsing the result into fewer rows.

C. They replace the need for JOIN operations.

D. They automatically create new tables for grouped data.

Solution

1. Step 1: Understand window function behavior

   Window functions perform calculations across a set of rows related to the current row but keep all original rows in the output.

2. Step 2: Compare with aggregation

   Unlike aggregation functions that reduce rows, window functions keep row details while adding calculated columns.

3. Final Answer:

   They allow calculations across rows without collapsing the result into fewer rows. -> Option B

4. Quick Check:

   Window functions keep rows and add calculations [OK]

Hint: Window functions keep all rows and add calculations [OK]

Common Mistakes:

• Thinking window functions reduce rows like GROUP BY
• Confusing window functions with JOINs
• Believing window functions only work on single rows

2. Which of the following is the correct syntax to calculate a running total of sales using a window function?

easy

A. SELECT sales, SUM(sales) FROM sales_table WHERE ORDER BY date;

B. SELECT sales, SUM(sales) GROUP BY date FROM sales_table;

C. SELECT sales, SUM(sales) OVER (ORDER BY date) FROM sales_table;

D. SELECT sales, SUM(sales) PARTITION BY date FROM sales_table;

Solution

1. Step 1: Identify window function syntax

   The correct syntax uses SUM() OVER() with ORDER BY inside OVER() to calculate running totals.

2. Step 2: Check each option

   SELECT sales, SUM(sales) OVER (ORDER BY date) FROM sales_table; correctly uses SUM(sales) OVER (ORDER BY date). Options A, B, and D misuse GROUP BY, WHERE, or PARTITION BY without OVER().

3. Final Answer:

   SELECT sales, SUM(sales) OVER (ORDER BY date) FROM sales_table; -> Option C

4. Quick Check:

   Running total uses SUM() OVER (ORDER BY ...) [OK]

Hint: Use OVER() with ORDER BY for running totals [OK]

Common Mistakes:

• Using GROUP BY instead of OVER()
• Placing ORDER BY outside OVER()
• Using PARTITION BY without OVER()

3. Given the table employees with columns department and salary, what will this query return?

SELECT department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

medium

A. Ranks employees within each department by salary, highest salary gets rank 1.

B. Ranks all employees together ignoring department, highest salary gets rank 1.

C. Calculates total salary per department.

D. Returns an error because RANK() cannot be used with PARTITION BY.

Solution

1. Step 1: Understand RANK() with PARTITION BY

   RANK() assigns ranks within each partition (department) ordered by salary descending.

2. Step 2: Analyze query output

   Each employee gets a rank starting at 1 for highest salary in their department, ties get same rank.

3. Final Answer:

   Ranks employees within each department by salary, highest salary gets rank 1. -> Option A

4. Quick Check:

   RANK() with PARTITION BY ranks within groups [OK]

Hint: RANK() with PARTITION BY ranks within groups [OK]

Common Mistakes:

• Thinking RANK() ignores PARTITION BY
• Confusing RANK() with aggregation
• Assuming RANK() causes errors with PARTITION BY

4. Identify the error in this query that tries to calculate a cumulative sum of sales per region:

SELECT region, sales, SUM(sales) OVER (PARTITION region ORDER BY date) AS running_total FROM sales_data;

medium

A. SUM() cannot be used as a window function.

B. Missing parentheses around PARTITION BY clause.

C. ORDER BY cannot be used inside OVER().

D. PARTITION keyword should be PARTITION BY.

Solution

1. Step 1: Check window function syntax

   The correct syntax requires PARTITION BY, not just PARTITION.

2. Step 2: Validate other parts

   ORDER BY inside OVER() is valid for running totals, and SUM() is allowed as a window function.

3. Final Answer:

   PARTITION keyword should be PARTITION BY. -> Option D

4. Quick Check:

   Use PARTITION BY, not PARTITION alone [OK]

Hint: Use PARTITION BY, not just PARTITION [OK]

Common Mistakes:

• Omitting BY after PARTITION
• Thinking ORDER BY is invalid in OVER()
• Believing SUM() can't be a window function

5. You want to find each employee's salary difference from the average salary in their department using window functions. Which query correctly achieves this?

hard

A. SELECT employee, salary, salary - AVG(salary) OVER (PARTITION BY department) AS diff FROM employees;

B. SELECT employee, salary, salary - AVG(salary) FROM employees GROUP BY department;

C. SELECT employee, salary, salary - AVG(salary) OVER () AS diff FROM employees;

D. SELECT employee, salary, salary - AVG(salary) PARTITION BY department FROM employees;

Solution

1. Step 1: Use AVG() as a window function partitioned by department

   AVG(salary) OVER (PARTITION BY department) calculates average salary per department without grouping rows.

2. Step 2: Subtract average from each employee's salary

   Subtracting this average from salary gives the difference per employee.

3. Final Answer:

   SELECT employee, salary, salary - AVG(salary) OVER (PARTITION BY department) AS diff FROM employees; -> Option A

4. Quick Check:

   Window AVG() with PARTITION BY calculates group average per row [OK]

Hint: Use AVG() OVER (PARTITION BY ...) for group averages per row [OK]

Common Mistakes:

• Using GROUP BY which collapses rows
• Omitting PARTITION BY causing overall average
• Incorrect syntax without OVER()