Top-N per group query in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Top 2 highest salaries per department

Given the Employees table with columns id, name, department, and salary, which query returns the top 2 highest salaries for each department?

SQL

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM Employees
) AS ranked
WHERE rn <= 2;

A SELECT department, name, salary FROM Employees WHERE salary IN (SELECT TOP 2 salary FROM Employees WHERE department = Employees.department ORDER BY salary DESC);

B SELECT department, name, salary FROM Employees ORDER BY department, salary DESC LIMIT 2;

C SELECT department, name, salary FROM Employees WHERE salary >= ALL (SELECT salary FROM Employees WHERE department = Employees.department ORDER BY salary DESC LIMIT 2);

D SELECT department, name, salary FROM (SELECT department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees) AS ranked WHERE rn <= 2;

💡 Hint

Use ROW_NUMBER() with PARTITION BY to rank salaries within each department.

Option D uses ROW_NUMBER() window function partitioned by department and ordered by salary descending, then filters to top 2 per group. Other options either use invalid syntax or do not correctly limit per group.

📝 Syntax

intermediate

Identify the syntax error in this top-N per group query

Which option contains a syntax error when trying to get the top 3 products by sales per category from a Products table with columns category, product_name, and sales?

SQL

SELECT category, product_name, sales
FROM (
  SELECT category, product_name, sales,
         RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk
  FROM Products
) AS ranked
WHERE rnk <= 3;

A SELECT category, product_name, sales FROM Products PARTITION BY category ORDER BY sales DESC LIMIT 3;

B SELECT category, product_name, sales FROM (SELECT category, product_name, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk FROM Products) AS ranked WHERE rnk <= 3;

C SELECT category, product_name, sales FROM Products WHERE sales IN (SELECT TOP 3 sales FROM Products WHERE category = Products.category ORDER BY sales DESC);

D SELECT category, product_name, sales FROM (SELECT category, product_name, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk FROM Products) ranked WHERE rnk <= 3;

💡 Hint

Check if PARTITION BY is allowed in the WHERE or FROM clause.

Option A uses PARTITION BY in the FROM clause without a window function, which is invalid SQL syntax. PARTITION BY is only valid inside window functions like ROW_NUMBER() or RANK().

❓ optimization

advanced

Optimizing top-N per group query for large datasets

You want to retrieve the top 5 orders by amount per customer from a large Orders table with columns customer_id, order_id, and amount. Which query is the most efficient?

A SELECT customer_id, order_id, amount FROM Orders WHERE order_id IN (SELECT TOP 5 order_id FROM Orders WHERE customer_id = Orders.customer_id ORDER BY amount DESC);

B SELECT customer_id, order_id, amount FROM (SELECT customer_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn FROM Orders) AS ranked WHERE rn <= 5;

C SELECT customer_id, order_id, amount FROM Orders ORDER BY customer_id, amount DESC LIMIT 5;

D SELECT customer_id, order_id, amount FROM Orders WHERE amount >= ALL (SELECT amount FROM Orders WHERE customer_id = Orders.customer_id ORDER BY amount DESC LIMIT 5);

💡 Hint

Window functions with partitioning are usually more efficient than correlated subqueries for top-N per group.

Option B uses ROW_NUMBER() window function which is optimized for large datasets and avoids correlated subqueries. Options B and D use correlated subqueries which are less efficient. Option B does not limit per group.

🧠 Conceptual

advanced

Understanding difference between RANK() and ROW_NUMBER() in top-N queries

Which statement correctly explains the difference between RANK() and ROW_NUMBER() when used to get top-N rows per group?

A <code>ROW_NUMBER()</code> assigns unique sequential numbers without gaps, while <code>RANK()</code> assigns the same rank to ties and skips subsequent ranks, possibly causing gaps.

B <code>RANK()</code> assigns unique sequential numbers without gaps, while <code>ROW_NUMBER()</code> assigns the same rank to ties and skips subsequent ranks.

C <code>RANK()</code> always returns the row number of the first row in the partition, while <code>ROW_NUMBER()</code> returns the total count of rows.

D <code>ROW_NUMBER()</code> and <code>RANK()</code> behave identically when ordering rows with ties.

💡 Hint

Think about how ties are handled in ranking functions.

ROW_NUMBER() gives a unique number to each row, even if values tie, so no gaps. RANK() gives the same rank to tied rows but skips ranks after ties, causing gaps.

🔧 Debug

expert

Why does this top-N per group query return fewer rows than expected?

You run this query to get the top 3 employees by salary per department from Employees table, but some departments return fewer than 3 rows. Why?

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
  FROM Employees
) AS ranked
WHERE drnk <= 3;

A Because <code>DENSE_RANK()</code> skips ranks after ties, causing some ranks to be missing and fewer rows returned.

B Because the WHERE clause should use <code>drnk < 3</code> instead of <code>drnk <= 3</code>.

C Because <code>DENSE_RANK()</code> assigns the same rank to ties without gaps, so if many employees tie at a rank, fewer than 3 ranks may cover fewer rows.

D Because the query needs to use <code>ROW_NUMBER()</code> instead of <code>DENSE_RANK()</code> to include all rows.

💡 Hint

Consider how DENSE_RANK() handles ties and how that affects the number of rows returned.

DENSE_RANK() assigns the same rank to tied salaries without gaps. If many employees share the same salary at a rank, the count of rows with ranks <= 3 may be less than 3 per department.

Practice

1. What does the ROW_NUMBER() OVER (PARTITION BY column ORDER BY column) clause do in a SQL query?

easy

A. Assigns a unique rank number to each row within each group defined by the partition column.

B. Counts the total number of rows in the entire table.

C. Filters rows to only include those with the highest value in the column.

D. Joins two tables based on the partition column.

Solution

1. Step 1: Understand ROW_NUMBER() function

   ROW_NUMBER() assigns a unique sequential number to rows in the result set.

2. Step 2: Understand PARTITION BY usage

   PARTITION BY divides rows into groups, and ROW_NUMBER() restarts numbering for each group.

3. Final Answer:

   Assigns a unique rank number to each row within each group defined by the partition column. -> Option A

4. Quick Check:

   ROW_NUMBER() with PARTITION BY = unique rank per group [OK]

Hint: ROW_NUMBER() with PARTITION BY ranks rows per group [OK]

Common Mistakes:

• Thinking ROW_NUMBER() counts total rows
• Confusing PARTITION BY with filtering
• Assuming it filters rows automatically

2. Which of the following SQL queries correctly selects the top 2 highest salaries per department from an employees table?

easy

A. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2;

B. SELECT * FROM employees ORDER BY salary DESC LIMIT 2 PARTITION BY department;

C. SELECT * FROM employees WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) <= 2;

D. SELECT TOP 2 * FROM employees PARTITION BY department ORDER BY salary DESC;

Solution

1. Step 1: Understand ROW_NUMBER() usage in subquery

   ROW_NUMBER() must be used in a subquery or CTE to assign ranks before filtering.

2. Step 2: Filter rows by rank in outer query

   The outer query filters rows where the rank (rn) is 2 or less per department.

3. Final Answer:

   SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2; -> Option A

4. Quick Check:

   Use subquery with ROW_NUMBER() and filter by rank [OK]

Hint: Use subquery with ROW_NUMBER() then filter by rank [OK]

Common Mistakes:

• Trying to filter ROW_NUMBER() directly in WHERE clause
• Using LIMIT with PARTITION BY (not valid syntax)
• Using TOP without proper partitioning

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

SELECT region, salesperson, amount FROM (SELECT region, salesperson, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales) AS ranked WHERE rn = 1;

medium

A. All sales records sorted by amount descending.

B. All sales records where amount equals 1.

C. The highest sale amount record for each region.

D. The lowest sale amount record for each region.

Solution

1. Step 1: Analyze ROW_NUMBER() with PARTITION BY region

   Rows are grouped by region and ranked by amount descending, so rn=1 is highest amount per region.

2. Step 2: Filter WHERE rn = 1

   This keeps only the top-ranked row per region, i.e., the highest sale amount record.

3. Final Answer:

   The highest sale amount record for each region. -> Option C

4. Quick Check:

   ROW_NUMBER() = 1 per group returns top record [OK]

Hint: Filter WHERE rn=1 to get top record per group [OK]

Common Mistakes:

• Thinking it returns all rows sorted
• Confusing rn=1 with lowest amount
• Assuming amount = 1 filters rows

4. Identify the error in this SQL query that tries to get the top 3 recent orders per customer from orders table:

SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rank FROM orders WHERE rank <= 3;

medium

A. Missing GROUP BY clause for customer_id.

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

C. ORDER BY must be ascending, not descending.

D. You cannot filter on an alias in the WHERE clause directly.

Solution

1. Step 1: Understand alias usage in WHERE clause

   Aliases like 'rank' defined in SELECT cannot be used in WHERE clause directly because WHERE is evaluated before SELECT.

2. Step 2: Correct approach using subquery or CTE

   Use a subquery or CTE to assign ROW_NUMBER(), then filter on 'rank' in outer query.

3. Final Answer:

   You cannot filter on an alias in the WHERE clause directly. -> Option D

4. Quick Check:

   Filter on ROW_NUMBER() alias in outer query, not WHERE [OK]

Hint: Filter ROW_NUMBER() in outer query, not WHERE clause [OK]

Common Mistakes:

• Using alias in WHERE clause directly
• Thinking ORDER BY DESC is invalid
• Adding unnecessary GROUP BY

5. You have a products table with columns category, product_id, and price. You want to find the top 2 most expensive products per category, but if two products have the same price, include both even if it means more than 2 per category. Which query achieves this?

hard

A. Use ROW_NUMBER() partitioned by category ordered by price DESC and filter WHERE rn <= 2.

B. Use RANK() partitioned by category ordered by price DESC and filter WHERE rank <= 2.

C. Use DENSE_RANK() partitioned by category ordered by price ASC and filter WHERE dense_rank <= 2.

D. Use COUNT() partitioned by category ordered by price DESC and filter WHERE count <= 2.

Solution

1. Step 1: Understand difference between ROW_NUMBER(), RANK(), and DENSE_RANK()

   ROW_NUMBER() assigns unique ranks, so ties get different numbers. RANK() assigns same rank to ties and skips ranks after. DENSE_RANK() assigns same rank to ties but no gaps.

2. Step 2: Choose RANK() to include ties at second place

   RANK() allows including all products tied at rank 2, possibly more than 2 rows per category.

3. Final Answer:

   Use RANK() partitioned by category ordered by price DESC and filter WHERE rank <= 2. -> Option B

4. Quick Check:

   RANK() includes ties with same rank [OK]

Hint: Use RANK() to include ties in top N per group [OK]

Common Mistakes:

• Using ROW_NUMBER() excludes ties
• Using DENSE_RANK() includes ties but no gaps
• Using COUNT() is invalid for ranking