Your manager asks you to find employees who earn more than the average salary in their own department.
Sounds simple, right?
But should you compare salaries with the company average or calculate a separate average for each department?
Let's look at the data.
employees
| emp_id | name | department | salary |
|---|---|---|---|
| 101 | Amit | IT | 90000 |
| 102 | Priya | IT | 70000 |
| 103 | Rahul | IT | 60000 |
| 104 | Neha | HR | 80000 |
| 105 | Sneha | HR | 60000 |
| 106 | Kiran | HR | 50000 |
| 107 | Meera | Finance | 75000 |
| 108 | Arjun | Finance | 55000 |
Your challenge
Find employees whose salary is greater than their department's average salary.
Which query would you choose?
Option A
SELECT name, department, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Option B
SELECT name, department, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
Option C
SELECT name, department, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
GROUP BY department
);
SQL Solution
Option B is correct.
SELECT name, department, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
Output
| name | department | salary |
|---|---|---|
| Amit | IT | 90000 |
| Neha | HR | 80000 |
| Meera | Finance | 75000 |
Why does this work?
The condition "department = e.department" calculates the average salary for the current employee's department.
This is called a correlated subquery because the inner query refers to a column from the outer query.
Want to learn more about SQL subqueries?
Your turn
Can you solve the same problem using "AVG() OVER (PARTITION BY department)"?
Would you choose a correlated subquery or a window function?
Share your answer in the comments.
