Find Employees Earning Above Department Average in SQL

Question

Given `employees` (emp_id, emp_name, department_id, salary), find all employees whose salary is strictly greater than the average salary of their respective department.

Accepted Answer

Optimal Solution:
WITH dept_averages AS (
  SELECT 
    department_id,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT 
  e.emp_id,
  e.emp_name,
  e.department_id,
  e.salary
FROM employees e
JOIN dept_averages da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary
ORDER BY e.department_id, e.salary DESC;

Technical Explanation:
A CTE precomputes the average salary for each department once. Joining back to employees allows direct filtering `salary > avg_salary`.

emp_id	emp_name	department_id	salary
1	Sachin	10	85000
2	Rahul	10	65000
3	Shreya	20	95000
4	Ananya	20	92000

emp_id	emp_name	department_id	salary
1	Sachin	10	85000
3	Shreya	20	95000