PARTITION BY for grouping windows in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROW_NUMBER() with PARTITION BY

Given the table sales with columns region, salesperson, and amount, what is the output of this query?

SELECT region, salesperson, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

PostgreSQL

CREATE TABLE sales (region TEXT, salesperson TEXT, amount INT);
INSERT INTO sales VALUES
('North', 'Alice', 500),
('North', 'Bob', 300),
('South', 'Carol', 700),
('South', 'Dave', 400);

SELECT region, salesperson, amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM sales;

A [{"region": "North", "salesperson": "Alice", "amount": 500, "rank": 1}, {"region": "North", "salesperson": "Bob", "amount": 300, "rank": 2}, {"region": "South", "salesperson": "Carol", "amount": 700, "rank": 1}, {"region": "South", "salesperson": "Dave", "amount": 400, "rank": 2}]

B [{"region": "North", "salesperson": "Alice", "amount": 500, "rank": 2}, {"region": "North", "salesperson": "Bob", "amount": 300, "rank": 1}, {"region": "South", "salesperson": "Carol", "amount": 700, "rank": 2}, {"region": "South", "salesperson": "Dave", "amount": 400, "rank": 1}]

C [{"region": "North", "salesperson": "Alice", "amount": 500, "rank": 1}, {"region": "North", "salesperson": "Bob", "amount": 300, "rank": 1}, {"region": "South", "salesperson": "Carol", "amount": 700, "rank": 1}, {"region": "South", "salesperson": "Dave", "amount": 400, "rank": 1}]

D [{"region": "North", "salesperson": "Alice", "amount": 500, "rank": 2}, {"region": "North", "salesperson": "Bob", "amount": 300, "rank": 2}, {"region": "South", "salesperson": "Carol", "amount": 700, "rank": 2}, {"region": "South", "salesperson": "Dave", "amount": 400, "rank": 2}]

💡 Hint

Remember that ROW_NUMBER() restarts numbering for each partition defined by PARTITION BY.

ROW_NUMBER() assigns a unique rank starting at 1 within each partition. Here, partitioning by region means numbering restarts for 'North' and 'South' separately, ordered by amount descending.

❓ query_result

intermediate

SUM() with PARTITION BY in window functions

What is the output of this query on the orders table with columns customer_id and order_total?

SELECT customer_id, order_total,
       SUM(order_total) OVER (PARTITION BY customer_id) AS total_per_customer
FROM orders
ORDER BY customer_id, order_total;

PostgreSQL

CREATE TABLE orders (customer_id INT, order_total INT);
INSERT INTO orders VALUES
(1, 100),
(1, 200),
(2, 150),
(2, 50);

SELECT customer_id, order_total,
       SUM(order_total) OVER (PARTITION BY customer_id) AS total_per_customer
FROM orders
ORDER BY customer_id, order_total;

A [{"customer_id": 1, "order_total": 100, "total_per_customer": 100}, {"customer_id": 1, "order_total": 200, "total_per_customer": 200}, {"customer_id": 2, "order_total": 50, "total_per_customer": 50}, {"customer_id": 2, "order_total": 150, "total_per_customer": 150}]

B [{"customer_id": 1, "order_total": 100, "total_per_customer": 300}, {"customer_id": 1, "order_total": 200, "total_per_customer": 300}, {"customer_id": 2, "order_total": 50, "total_per_customer": 200}, {"customer_id": 2, "order_total": 150, "total_per_customer": 200}]

C [{"customer_id": 1, "order_total": 100, "total_per_customer": 100}, {"customer_id": 1, "order_total": 200, "total_per_customer": 300}, {"customer_id": 2, "order_total": 50, "total_per_customer": 50}, {"customer_id": 2, "order_total": 150, "total_per_customer": 200}]

D [{"customer_id": 1, "order_total": 100, "total_per_customer": 300}, {"customer_id": 1, "order_total": 200, "total_per_customer": 200}, {"customer_id": 2, "order_total": 50, "total_per_customer": 200}, {"customer_id": 2, "order_total": 150, "total_per_customer": 150}]

💡 Hint

SUM() with PARTITION BY calculates the total for each group and repeats it for every row in that group.

The window SUM() adds all order_totals per customer_id and shows the same total for each row of that customer.

📝 Syntax

advanced

Identify the syntax error in window function usage

Which option contains a syntax error in using PARTITION BY in a window function?

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

A RANK() OVER (PARTITION BY department ORDER BY salary DESC)

B RANK() OVER (PARTITION BY department ORDER BY salary DESC NULLS FIRST)

C RANK() OVER (PARTITION BY department ORDER BY salary DESC NULLS LAST)

D RANK() OVER (PARTITION department ORDER BY salary DESC)

💡 Hint

Check the keyword used for grouping in window functions.

The correct keyword is PARTITION BY, not PARTITION alone. Option D misses the BY keyword causing syntax error.

❓ optimization

advanced

Optimizing window function with PARTITION BY

You want to calculate the average sales per region and show it alongside each sale. Which query is more efficient?

A) SELECT region, sale_amount,
          AVG(sale_amount) OVER (PARTITION BY region) AS avg_region_sale
   FROM sales;

B) SELECT s.region, s.sale_amount, r.avg_sale
   FROM sales s
   JOIN (SELECT region, AVG(sale_amount) AS avg_sale FROM sales GROUP BY region) r ON s.region = r.region;

A Query A is more efficient because window functions avoid extra joins and scan the table once.

B Query B is more efficient because JOIN with aggregation is faster than window functions.

C Both queries have the same efficiency because they produce the same result.

D Query B is more efficient because it uses GROUP BY which is always faster than window functions.

💡 Hint

Window functions compute aggregates without extra joins, often improving performance.

Window functions compute aggregates over partitions in the same scan, avoiding costly joins and improving efficiency.

🧠 Conceptual

expert

Understanding difference between PARTITION BY and GROUP BY

Which statement correctly explains the difference between PARTITION BY in window functions and GROUP BY in SQL?

A Both PARTITION BY and GROUP BY produce the same output but use different syntax.

B PARTITION BY aggregates rows into one row per group; GROUP BY keeps all rows but divides them into groups.

C PARTITION BY divides rows into groups but keeps all rows in the output; GROUP BY aggregates rows into one row per group.

D PARTITION BY is used only for filtering rows; GROUP BY is used only for sorting rows.

💡 Hint

Think about whether the number of rows changes after applying each clause.

PARTITION BY groups rows for window functions but returns all original rows with extra info; GROUP BY reduces rows to one per group by aggregation.