"Earns More Than Their Manager"

Concepts covered: Self-Join, Filtering, Inner Joins, Sorting Results

Once the join has put e.salary and m.salary on the same output row, the question collapses into a single comparison. The relationship goes in the ON clause, the question's filter goes in WHERE, and the SELECT names both roles so the interviewer can verify each row by eye. The hard part was already done in the FROM clause.

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m
  ON e.manager_id = m.employee_id
 AND e.salary > m.salary

employees
employee_id	name	manager_id	salary
1	Alice	NULL	200000
2	Bob	1	180000
3	Carol	1	220000

The run uses only the first 3 rows of the table, Alice and her 2 direct reports, and puts the salary test inside ON next to the pointer; for an inner join that keeps exactly the rows the WHERE version keeps, a point the follow-ups below come back to. Bob against Alice: the pointer matches, but 180000 > 200000 is false, so the pair is dropped. Carol against Alice: 220000 > 200000 is true, kept. Alice finds no partner at all. One row comes back: Carol, manager Alice. On the full 4-row table, Dave against Bob adds one more test; 150000 > 180000 is false, and the answer is still Carol alone. Narrate the trace row by row in the room, because the interviewer cannot see it unless you say it.

employee	employee_salary	manager	manager_salary	kept
Bob	180000	Alice	200000	no
Carol	220000	Alice	200000	yes
Dave	150000	Bob	180000	no

The version worth writing in full

SELECT
  e.employee_id,
  e.name              AS employee_name,
  e.salary            AS employee_salary,
  m.name              AS manager_name,
  m.salary            AS manager_salary,
  e.salary - m.salary AS gap
FROM employees e
JOIN employees m
  ON e.manager_id = m.employee_id
WHERE e.salary > m.salary
ORDER BY gap DESC;

The question asked only for names, and the names-only query is a complete answer. The fuller version is what you write when the interviewer says 'good, now show me the manager too' or 'by how much?', and it is worth having ready because those follow-ups come in that order nearly every time. The gap column is one subtraction across the 2 roles, and ORDER BY on it puts the biggest inversion at the top, which is what a compensation analyst actually wants from this query.

Which way the comparison points

e.salary > m.salary and m.salary > e.salary are both valid SQL and answer opposite questions. The first finds employees out-earning their manager. The second finds employees paid less than their manager, which on most org charts is nearly everybody. Read the comparison back as a sentence with the roles substituted in: 'employee salary greater than manager salary'. If the sentence matches the question, the direction is right. On the 4-row table the first returns Carol and the second returns Bob and Dave, so even the row count tells you which one you wrote.

Saying the CEO line unprompted

Alice's absence from the result is correct, and the interviewer wants to hear you say so without being asked. The sentence: 'Alice has no manager, so the inner join drops her, which is right for this question; if the question wanted every employee listed, I would switch to a LEFT JOIN and the manager columns would be NULL for her.' That one sentence names the NULL and the join type, and it says the choice depends on the question. Candidates who stay silent about the CEO are usually asked about it, and answering under questioning scores lower than volunteering it.

The follow-ups and their answers

Questions that arrive after the first query works

'Two people report to Alice. Does that break anything?' No: Alice's m row is reused for Bob and for Carol, and each employee still gets exactly 1 output row.
'What if a manager_id points at an employee who was deleted?' The inner join drops that employee, exactly as it drops the CEO; a LEFT JOIN shows them with a NULL manager, which exposes the dirty data.
'What about someone paid the same as their manager?' > excludes them and >= includes them. Ask which the question means before choosing.
'Can you show the manager's manager?' A second JOIN of employees, aliased mm, on m.manager_id = mm.employee_id. Walking further up the chart is its own topic.
'Why is the salary test in WHERE and not in ON?' For an inner join the result is identical; WHERE keeps the relationship and the question apart, which is easier to read and to change.

About this lesson

This section is part of the Self-Join: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.