SQL Challenge.
Find Departments with an Average Salary Above the Company Average
Here’s a practical SQL interview question. Can you find which departments have an average salary higher than the company-wide average?
Table 1: employees
| emp_id | dept_id | salary |
|---|---|---|
| 101 | 10 | 80000 |
| 102 | 10 | 100000 |
| 103 | 10 | 90000 |
| 104 | 20 | 50000 |
| 105 | 20 | 60000 |
| 106 | 20 | 70000 |
| 107 | 30 | 40000 |
| 108 | 30 | 50000 |
Table 2: departments
| id | name |
|---|---|
| 10 | IT |
| 20 | HR |
| 30 | Sales |
Your task: Write an SQL query to find departments whose average salary is higher than the overall company average.
Solution
WITH department_averages AS (
SELECT
e.dept_id,
d.name AS dept,
AVG(e.salary) AS average_salary
FROM employees e
JOIN departments d
ON e.dept_id = d.id
GROUP BY e.dept_id, d.name
)
SELECT
dept_id,
dept,
average_salary
FROM department_averages
WHERE average_salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY average_salary DESC;
Expected Output
| dept_id | dept | average_salary |
|---|---|---|
| 10 | IT | 90000 |
The company-wide average salary is ₹67,500. IT has an average salary of ₹90,000, while HR and Sales have lower averages.
The CTE calculates the average salary for each department, and the subquery calculates the overall company average. The final query returns only departments whose average is higher than that benchmark.
Interview Challenge
What happens if a new employee earning ₹120,000 joins HR?
Will HR also appear in the result, or will IT remain the only department?
Share your answer in the comments.
For more SQL practice involving joins and multiple tables, visit this guide:
Follow Sankalan Data Tech for more practical SQL examples and interview questions.
Top comments (0)