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.

Practice

1. What does the PARTITION BY clause do in a window function in PostgreSQL?

easy

A. It filters rows before the window function is applied.

B. It divides the rows into groups to perform calculations within each group.

C. It sorts the entire table before applying the window function.

D. It removes duplicate rows from the result set.

Solution

1. Step 1: Understand the role of PARTITION BY

   PARTITION BY groups rows so window functions calculate results within each group separately.

2. Step 2: Differentiate from filtering and sorting

   Filtering removes rows, sorting orders rows, but PARTITION BY only groups rows without removing or sorting them globally.

3. Final Answer:

   It divides the rows into groups to perform calculations within each group. -> Option B

4. Quick Check:

   PARTITION BY = grouping rows for window functions [OK]

Hint: Remember: PARTITION BY groups rows, it does not filter or sort [OK]

Common Mistakes:

• Confusing PARTITION BY with WHERE filtering
• Thinking PARTITION BY sorts all rows
• Assuming PARTITION BY removes duplicates

2. Which of the following is the correct syntax to use PARTITION BY in a window function?

easy

A. SELECT col, SUM(value) OVER (PARTITION BY col ORDER BY date) FROM table;

B. SELECT col, SUM(value) PARTITION BY col FROM table;

C. SELECT col, SUM(value) OVER PARTITION BY col FROM table;

D. SELECT col, SUM(value) WHERE PARTITION BY col FROM table;

Solution

1. Step 1: Identify correct window function syntax

   The correct syntax uses OVER() with PARTITION BY inside parentheses.

2. Step 2: Check each option

   SELECT col, SUM(value) OVER (PARTITION BY col ORDER BY date) FROM table; correctly uses SUM(value) OVER (PARTITION BY col ORDER BY date). Others misuse PARTITION BY outside OVER or with WHERE.

3. Final Answer:

   SELECT col, SUM(value) OVER (PARTITION BY col ORDER BY date) FROM table; -> Option A

4. Quick Check:

   Window functions use OVER() with PARTITION BY inside [OK]

Hint: PARTITION BY must be inside OVER() parentheses [OK]

Common Mistakes:

• Placing PARTITION BY outside OVER()
• Using WHERE instead of PARTITION BY
• Omitting OVER() entirely

3. Given the table sales with columns region, month, and amount, what will this query return?

SELECT region, month, amount, SUM(amount) OVER (PARTITION BY region ORDER BY month) AS running_total FROM sales ORDER BY region, month;

medium

A. Monthly sales amount without any cumulative calculation.

B. Total sales amount for all regions combined.

C. An error because PARTITION BY cannot be combined with ORDER BY.

D. A running total of sales amount per region ordered by month.

Solution

1. Step 1: Understand SUM() OVER with PARTITION BY and ORDER BY

   SUM() OVER (PARTITION BY region ORDER BY month) calculates a running total per region ordered by month.

2. Step 2: Analyze the output

   Each row shows the cumulative sum of amount for that region up to the current month.

3. Final Answer:

   A running total of sales amount per region ordered by month. -> Option D

4. Quick Check:

   PARTITION BY groups, ORDER BY defines running total order [OK]

Hint: Running totals use PARTITION BY with ORDER BY inside OVER() [OK]

Common Mistakes:

• Thinking it sums all regions together
• Ignoring ORDER BY for running total
• Assuming syntax error with ORDER BY in window

4. Identify the error in this query:

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

medium

A. Missing parentheses around PARTITION BY and ORDER BY clauses.

B. RANK() cannot be used with PARTITION BY.

C. ORDER BY cannot be used inside window functions.

D. employee_id must be included in PARTITION BY.

Solution

1. Step 1: Check window function syntax

   Window functions require OVER() with parentheses enclosing PARTITION BY and ORDER BY.

2. Step 2: Identify missing parentheses

   The query misses parentheses after OVER, causing syntax error.

3. Final Answer:

   Missing parentheses around PARTITION BY and ORDER BY clauses. -> Option A

4. Quick Check:

   OVER() must enclose PARTITION BY and ORDER BY [OK]

Hint: Always use parentheses after OVER() for window clauses [OK]

Common Mistakes:

• Omitting parentheses after OVER
• Thinking ORDER BY is not allowed in window functions
• Assuming all columns must be in PARTITION BY

5. You want to calculate the average sales per product category and also show each sale's rank within its category by sale date. Which query correctly uses PARTITION BY to achieve this?

hard

A. SELECT product, category, sale_date, amount, AVG(amount) OVER (PARTITION BY sale_date), RANK() OVER (PARTITION BY product ORDER BY category) FROM sales;

B. SELECT product, category, sale_date, amount, AVG(amount) PARTITION BY category, RANK() PARTITION BY category ORDER BY sale_date FROM sales;

C. SELECT product, category, sale_date, amount, AVG(amount) OVER (PARTITION BY category), RANK() OVER (PARTITION BY category ORDER BY sale_date) FROM sales;

D. SELECT product, category, sale_date, amount, AVG(amount) OVER (ORDER BY category), RANK() OVER (ORDER BY sale_date) FROM sales;

Solution

1. Step 1: Use AVG() with PARTITION BY category

   AVG(amount) OVER (PARTITION BY category) calculates average sales per category.

2. Step 2: Use RANK() with PARTITION BY category ordered by sale_date

   RANK() OVER (PARTITION BY category ORDER BY sale_date) ranks sales within each category by date.

3. Step 3: Verify syntax correctness

   SELECT product, category, sale_date, amount, AVG(amount) OVER (PARTITION BY category), RANK() OVER (PARTITION BY category ORDER BY sale_date) FROM sales; uses correct OVER() syntax with PARTITION BY and ORDER BY clauses for both functions.

4. Final Answer:

   SELECT product, category, sale_date, amount, AVG(amount) OVER (PARTITION BY category), RANK() OVER (PARTITION BY category ORDER BY sale_date) FROM sales; -> Option C

5. Quick Check:

   Use PARTITION BY category inside OVER() for grouping [OK]

Hint: Use OVER(PARTITION BY category) for grouping in window functions [OK]

Common Mistakes:

• Omitting OVER() or PARTITION BY
• Mixing PARTITION BY columns incorrectly
• Using PARTITION BY without parentheses