SQL Quest › SQL Interview Questions › NULL Handling
Out-Earning the Boss: What COUNT Counts
HR wants a per-department look at how pay sits against the manager's. Join each employee in employees to their manager through manager_id — and keep the people who have no manager, because they are part of the headcount.
Show exactly these 6 columns, in this order: department, headcount (everyone in the department), with_manager (employees who have a manager), top_level (employees with no manager), out_earn_manager (employees paid more than their manager) and avg_gap_to_manager (the average of manager salary minus employee salary, over the employees who have a manager, rounded to 0 decimals). Order by department.
Seven employees have a NULL manager_id, and every column asks you to decide what happens to them:
- COUNT(*) counts every row; COUNT(column) skips the rows where that column is NULL. Two of these columns are a single COUNT each, and a third is the difference between two.
- For someone with no manager, e.salary > m.salary is neither true nor false — it is NULL. A CASE WHEN … THEN 1 ELSE 0 END sends NULL to the ELSE branch, which is right here. Write the same test the other way round — WHEN e.salary <= m.salary THEN 0 ELSE 1 — and every top-level person is counted as out-earning a manager they do not have.
- AVG skips NULLs, so the gap needs no filter. Replace the missing manager salary with 0 and the average collapses: each top-level person becomes a gap of minus their own salary.
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: 5 rows — Finance is the only department where someone out-earns their manager
Hint
Concepts
SELECT NULL Handling LEFT JOIN Self-Join COUNT AVG CASE GROUP BY
Practise the topic: SQL practice questions · NULL handling practice · JOIN practice · GROUP BY exercises · CASE WHEN practice
Read the concept: NULL handling mistakes
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