learn deep. ship safe.Practice testsFollow ByteMunSubscribe

hardWindow functionsSQL

Top Three Salaries per Department

List everyone whose salary is among the top three distinct salaries in their department, using DENSE_RANK() with PARTITION BY: a senior SQL interview staple.

SQL

Tables: departments(id, name) and employees(id, name, salary, manager_id, dept_id).

A department's high earners are the employees whose salary is one of the three highest distinct salaries in that department. Write a query that returns every high earner with three columns in this order: department, employee and salary. Any row order is fine.

Examples

Example 1

Input: IT salaries: Ana 900, Bo 850, Cy 850, Di 700, Ed 600
Output: department | employee | salary
IT | Ana | 900
IT | Bo | 850
IT | Cy | 850
IT | Di | 700
Why: The distinct salaries are 900, 850, 700, 600. The top three are 900, 850 and 700, so four people qualify; Ed's 600 is fourth.

Constraints

  • Ties count once when finding the three salary levels, but everyone on those levels is returned.
  • A department with fewer than three distinct salaries returns all its employees.
  • Departments without employees don't appear.

Hints

Hint 1

Which ranking function gives tied salaries the same rank and leaves no gaps?

Hint 2

Rank inside each department with PARTITION BY dept_id.

Hint 3

Window functions can't be filtered in WHERE directly: rank in a CTE, then filter on the rank.

Solution

Show the solution (try it yourself first)

Approach: DENSE_RANK per department

DENSE_RANK() numbers distinct salary levels 1, 2, 3 within each department, giving tied salaries the same number with no gaps, which matches the definition exactly. RANK() would skip numbers after a tie (900, 850, 850, 700 would rank 1, 2, 2, 4) and drop the 700 earner; ROW_NUMBER() would cut ties arbitrarily.

WITH ranked AS (
  SELECT e.name, e.salary, e.dept_id,
         DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS r
  FROM employees e
)
SELECT d.name AS department, ranked.name AS employee, ranked.salary
FROM ranked
JOIN departments d ON d.id = ranked.dept_id
WHERE r <= 3;

Without window functions, count the distinct higher salaries in the same department: WHERE (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id AND e2.salary > e.salary) < 3. It is correct but O(n²) in the worst case.

Complexity

O(n log n) to sort each partition; an index on (dept_id, salary) helps MySQL avoid a separate sort.

Found a mistake in this problem, its tests or the solution?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table departments
idname
1IT
Table employees
idnamesalarymanager_iddept_id
1Ana900NULL1
2Bo850NULL1
3Cy850NULL1
4Di700NULL1
5Ed600NULL1
Expected (4 rows)
ITAna900
ITBo850
ITCy850
ITDi700
Case 2
Table departments
idname
1HR
2Ops
Table employees
idnamesalarymanager_iddept_id
1Fia500NULL1
2Gil400NULL1
3Hu300NULL2
Expected (3 rows)
HRFia500
HRGil400
OpsHu300

Run checks these 2 samples. Submit checks all 5 tests, including hidden ones.

  1. hard
  2. Daily Sales Running TotalWindow functions · GROUP BY and aggregates · SQL
    medium
  3. Highest Paid Employee in Each DepartmentJoins · GROUP BY and aggregates · Window functions · SQL
    medium
  4. Find Duplicate EmailsGROUP BY and aggregates · SQL
    easy