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.