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.