SQL Interview Series — Part 6

📌 Question 6: Find the Top 2 Highest-Paid Employees in Each Department

Suppose you have:

Employee

employee_id employee_name department salary

1 Amit IT 60000

2 Rahul IT 90000

3 Priya IT 80000

4 Neha HR 70000

5 Raj HR 50000

6 Karan HR 90000

❓ Find the top 2 highest-paid employees from each department.

💡 Approach

This is a classic window

-function interview question.

We need to:

1️⃣ Divide employees by department.

2️⃣ Rank employees by salary within each department.

3️⃣ Keep only employees with rank ≤ 2.

SQL Solution

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rn
    FROM Employee
) e
WHERE rn <= 2;

🔎 How does it work?

PARTITION BY department

Creates a separate ranking for each department.

ORDER BY salary DESC

Places the highest salary first.

ROW_NUMBER()

Assigns 1, 2, 3... within each department.

Finally:

WHERE rn <= 2

keeps the top two employees from each department.

Expected Result

employee_name department salary

Rahul IT 90000

Priya IT 80000

Karan HR 90000

Neha HR 70000

⚠️ Interview Trap

What if two employees have the same salary?

For example:

Rahul 90000

Priya 90000

Amit 80000

ROW_NUMBER() will still assign unique numbers:

Rahul 1

Priya 2

Amit 3

Double Tap ❤️ For More