SQL Interview Series — Part 12

📌 Question 12: Find the Department with the Highest Average Salary

Suppose you have an Employee table:

employee_id | employee_name | department | salary
------------|---------------|------------|-------
1           | Amit          | IT         | 60000
2           | Rahul         | IT         | 80000
3           | Priya         | HR         | 70000
4           | Neha          | HR         | 90000
5           | Raj           | Finance    | 85000
6           | Karan         | Finance    | 95000

❓ Find the department that has the highest average salary.

💡 Approach

First, calculate the average salary for every department.

Then, find the department with the highest average.

Department averages:

IT → (60000 + 80000) / 2 = 70000

HR → (70000 + 90000) / 2 = 80000

Finance → (85000 + 95000) / 2 = 90000

Therefore:

Finance → 90000

📌 SQL Solution

SELECT
    department,
    AVG(salary) AS avg_salary
FROM Employee
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

Expected result:

department | avg_salary
-----------|-----------
Finance    | 90000

🔎 How does it work?

1️⃣ "GROUP BY department"

Creates one group for each department.

2️⃣ "AVG(salary)"

Calculates the average salary within each department.

3️⃣ "ORDER BY avg_salary DESC"

Places the department with the highest average first.

4️⃣ "LIMIT 1"

Returns only the top department.

⚠️ Interview Trap

What if two departments have the same highest average salary?

For example:

IT → 80000

HR → 80000

Finance → 75000

"LIMIT 1" would return only one department.

If the interviewer asks:

👉 "Find all departments having the highest average salary"

then use a ranking approach:

SELECT department, avg_salary
FROM (
    SELECT
        department,
        AVG(salary) AS avg_salary,
        DENSE_RANK() OVER (
            ORDER BY AVG(salary) DESC
        ) AS rnk
    FROM Employee
    GROUP BY department
) d
WHERE rnk = 1;

Now both IT and HR would be returned.

🎯 Double Tap ❤️ For More