SQL Quest › SQL Interview Questions › Subqueries & CTEs
Above-Department-Average Earners
Comp benchmarking classic. Find every employee whose salary is higher than the average salary of their own department — not the company-wide average. The same dollar figure can be "above average" in one department and "below average" in another, which is exactly the point of the comparison.
Show name, department, salary, dept_avg (rounded to nearest dollar). Order by salary descending, then name ascending.
This is a correlated subquery: the inner query references the outer table's department, so it has to be re-evaluated for every row. Beginners try to write WHERE salary > AVG(salary) directly — that fails because AVG is an aggregate that needs a GROUP BY scope. The fix is to push the AVG into a subquery that filters to the same department as the outer row.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
employees
| emp_id | name | department | position | salary | hire_date | manager_id | performance_rating |
|---|---|---|---|---|---|---|---|
| 1 | Alice Johnson | Engineering | Senior Developer | 95000 | 2019-03-15 | 5 | 4.5 |
| 2 | Bob Smith | Engineering | Developer | 75000 | 2020-06-01 | 1 | 3.8 |
| 3 | Carol Williams | Marketing | Marketing Manager | 85000 | 2018-09-20 | NULL | 4.2 |
Expected output: Both rows appear (each above their own dept avg)
Hint
Concepts
SELECT Subquery Correlated Subquery
Practise the topic: SQL practice questions · CTE practice
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test