Top 3 Salaries per Department

Hook: Interviewers love this one because it checks whether you can rank inside groups, not just sort one big table.

Question: How do you return the top 3 salaries per department in SQL?

Answer: Use a window function such as ROW_NUMBER() or DENSE_RANK() with PARTITION BY department and sort salaries descending. That gives each employee a rank inside their own department, so you can keep only the first 3 rows or the first 3 salary levels. The key detail is whether ties should be included.

Interview-Ready Answer: I would partition the employees by department, order each department by salary descending, and assign a window rank. If the requirement means the top 3 salary levels including ties, I’d use DENSE_RANK() and filter to ranks <= 3; if it means exactly 3 rows per department, I’d use ROW_NUMBER(). That is clean, readable, and much safer than writing separate queries per department.

What this really means

“Top 3 salaries per department” means every department gets its own leaderboard. You are not ranking the whole company; you are ranking only within each department. The subtle part is the word top: if two people tie for third place, do you want both of them or only one row? In interviews, say your assumption out loud.

How it works under the hood

1. Read the full employee list.
2. Split the rows into separate groups using PARTITION BY department. A partition is just a mini-table inside the window function.
3. Sort each department by salary from high to low with ORDER BY salary DESC.
4. Assign a number or rank to each row inside that department.
5. Filter to the rows you want: <= 3.
6. Sort the final output so it is easy to read, usually by department and then salary descending.

Memory model: think of each department as a separate podium. You hand out gold, silver, and bronze inside each room, not across the whole building.

Which ranking function should you use?

For this interview question, DENSE_RANK() is usually the safest answer because “top 3 salaries” often means the top 3 distinct salary values, and all employees tied at those values should appear. If the interviewer says “exactly three employees per department,” switch to ROW_NUMBER() and add a stable tie-breaker like employee_id.

Why this is better than older patterns

Before window functions, people used correlated subqueries or self-joins. Those can work, but they are harder to read and often slower on big tables. A modern window-function query is usually the cleanest interview solution and the one most teams would ship today.

Performance and complexity

In practice, this is usually dominated by sorting. A rough mental model is O(n log n) overall, because the database must order rows inside each department. On a table with 1 million employees across 200 departments, that is still very manageable on a modern server, but if memory is tight the sort can spill to disk and slow down.

An index on (department, salary DESC) can help the optimizer, especially if the query returns only a few columns. But an index is not magic; the engine may still sort depending on the plan, the selected columns, and the database version. If window functions are unavailable, such as on older MySQL 5.7-style systems, you would need a more manual approach with a self-join or correlated subquery.

Edge cases to call out

• Fewer than 3 employees in a department: return whatever rows exist; do not invent missing ranks.
• Ties at the boundary: decide whether to include all tied rows or only one row.
• Null salaries: usually ignore them, because unknown pay should not win a salary contest.
• Duplicate employee rows: if your source data is messy, deduplicate before ranking or your leaderboard will be wrong.

Real-World Example: Imagine a SaaS payroll dashboard that shows each department’s top earners to finance managers. The query powers a monthly report used for bonus planning, so accuracy matters. If the team uses plain LIMIT 3, the dashboard shows the top 3 salaries across the whole company, which is obviously wrong but easy to miss in testing because the query still returns rows.

Now add ties. Suppose two engineers share the third-highest salary. If the business wants both names shown, DENSE_RANK() is correct. If a developer mistakenly uses ROW_NUMBER(), one employee disappears from the report, and finance thinks the data is incomplete. The symptoms are subtle: no SQL error, but managers file tickets because the headcount and bonus numbers do not match the HR system.

In production, that kind of bug shows up as a quiet trust problem. The dashboard looks polished, but the numbers do not reconcile. That is exactly why interviewers ask this question: they want to see whether you can turn a vague business phrase into a precise SQL rule.

Follow-up & Tricky Questions:

• How do you handle ties? If ties should all count, use DENSE_RANK(). If you want exactly three employees, use ROW_NUMBER() with a deterministic tie-breaker like employee_id.
• What is the difference between RANK() and DENSE_RANK()? RANK() leaves gaps after ties, while DENSE_RANK() does not. For “top 3 salary levels,” DENSE_RANK() is usually easier to reason about.
• How would you return the department name and employee name too? Just select those columns from the ranked result before filtering. The ranking happens first; the final projection decides which columns you display.
• How do you make the result deterministic? Add a secondary sort key, such as employee_id, in the ROW_NUMBER() order clause. That prevents the database from choosing a random winner when salaries tie.
• How would you optimize this on a huge table? A composite index on (department, salary DESC) often helps, but the main cost is still sorting. If the table is very large, check the execution plan and watch for disk spills.
• Why not use LIMIT 3? Because LIMIT applies to the whole result set, not each department. It gives you three rows total, which is the wrong shape for this problem.
• Why not use GROUP BY department and MAX(salary)? That only gives one salary per department. You lose the employee rows, and you cannot get the second and third places from a single MAX.
• What if a department has only 1 or 2 employees? Return those rows only. The query should not fail, and you should not pad missing spots with fake data.

Tricky gotchas:

• Should null salaries appear? Usually no. A null salary is unknown, not a real value, so it should be filtered out before ranking.
• Does ROW_NUMBER() include all ties? No. It picks one row at a time, so ties are broken by the ORDER BY list.
• Is “top 3 salaries” the same as “top 3 employees”? Not always. The first is about salary levels; the second is about individual rows. Clarify the requirement before you code.

Common Mistakes:

• Using LIMIT 3: This returns three rows total, not three per department. Correction: use PARTITION BY department with a window function.
• Choosing the wrong ranking function: ROW_NUMBER() and DENSE_RANK() answer different business questions. Correction: say whether you want exactly three rows or the top three salary levels.
• Ignoring ties: A tie at the boundary can change the row count. Correction: decide tie behavior before you filter.
• Not handling nulls: Null pay can sneak into the result if you do not filter it. Correction: exclude null salaries unless the business says otherwise.

Memory Hook: Think of each department as its own podium: gold, silver, bronze. You do not mix the podiums together; you award medals in every room separately.

Cheat Sheet:

• PARTITION BY department = separate leaderboard per department.
• ORDER BY salary DESC = highest salary first.
• ROW_NUMBER() = exactly 3 rows, ties broken.
• DENSE_RANK() = top 3 salary levels, ties included.
• Filter with <= 3 after ranking, not before.
• For stability, add a secondary sort key like employee_id.

Practice Tasks:

• Write the query using ROW_NUMBER() instead of DENSE_RANK().
• Modify the query to return the top 2 salaries per department.
• Change the sample data so one department has a tie for third place, and verify how the row count changes.