Top-N per group query in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the top 1 salary per department.

SQL

SELECT department, employee, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = [1]);

Drag options to blanks, or click blank then click option'

Adepartment

Bemployee

Csalary

Dposition

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee instead of department in the subquery condition.

Using salary instead of department in the subquery condition.

The subquery filters salaries by the same department as the outer query, so we use department to match groups.

2fill in blank

medium

Complete the code to assign row numbers partitioned by department ordered by salary descending.

SQL

SELECT department, employee, salary, ROW_NUMBER() OVER (PARTITION BY [1] ORDER BY salary DESC) AS rn FROM employees;

Drag options to blanks, or click blank then click option'

Adepartment

Bposition

Csalary

Demployee

Attempts:

3 left

💡 Hint

Common Mistakes

Partitioning by employee instead of department.

Partitioning by salary which does not group employees.

We partition by department to restart row numbers for each department group.

3fill in blank

hard

Fix the error in the query to select top 3 salaries per department using ROW_NUMBER.

SQL

SELECT department, employee, salary FROM (SELECT department, employee, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY [1] DESC) AS rn FROM employees) sub WHERE rn <= 3;

Drag options to blanks, or click blank then click option'

Aemployee

Bdepartment

Csalary

Dposition

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by employee instead of salary.

Ordering by department which does not rank salaries.

We order by salary descending to rank employees by highest salary per department.

4fill in blank

hard

Fill both blanks to select the top 2 employees by salary per department using DENSE_RANK.

SQL

SELECT department, employee, salary FROM (SELECT department, employee, salary, DENSE_RANK() OVER (PARTITION BY [1] ORDER BY [2] DESC) AS rank FROM employees) sub WHERE rank <= 2;

Drag options to blanks, or click blank then click option'

Adepartment

Bemployee

Csalary

Dposition

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping partition and order columns.

Using employee instead of department for partition.

Partition by department to group employees, and order by salary descending to rank top salaries.

5fill in blank

hard

Fill all three blanks to select the top 1 salary per department using a correlated subquery.

SQL

SELECT department, employee, salary FROM employees e1 WHERE salary = (SELECT MAX([1]) FROM employees e2 WHERE e2.[2] = e1.[3]);

Drag options to blanks, or click blank then click option'

Asalary

Bdepartment

Cemployee

Dposition

Attempts:

3 left

💡 Hint

Common Mistakes

Using employee instead of salary in MAX.

Comparing employee columns instead of department.

The subquery finds the max salary for the same department as the outer query.