✏️ Explanatory Question
Level: Coding Round — A LeetCode classic; tests whether you can join a table to itself to compare related rows.
The Puzzle: Given an employees table where each row has a manager_id referencing another employee, return the names of all employees who earn more than their own manager.
employees| id | name | salary | manager_id |
|---|---|---|---|
| 1 | Ajay | 90000 | NULL |
| 2 | Rumman | 95000 | 1 |
| 3 | Krushna | 60000 | 1 |
| 4 | Swetha | 85000 | 2 |
| name |
|---|
| Rumman |
Rumman earns 95,000 vs manager Ajay's 90,000. Swetha (85,000) earns less than manager Rumman (95,000), so she is excluded.
The key idea — self-join: Since both the employee and the manager live in the same table, you join employees to itself using two aliases: one representing the employee (e) and one representing the manager (m), linked by e.manager_id = m.id.
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id -- m = the employee's manager
WHERE e.salary > m.salary; -- employee earns more than managerSELECT e.name
FROM employees e
WHERE e.salary > (
SELECT m.salary FROM employees m WHERE m.id = e.manager_id
);
-- Works, but the self-join is usually cleaner and faster Edge case to mention: The CEO (Ajay) has manager_id = NULL. The JOIN naturally excludes him because NULL = m.id never matches — which is correct, since a top-level employee has no manager to compare against. Point this out to the interviewer to show you thought about NULLs.
Interviewer follow-up: "Also show the manager's name and the salary difference." → Add columns from the joined manager alias: SELECT e.name AS employee, m.name AS manager, e.salary - m.salary AS diff. The self-join already has both rows available, so extending the output is trivial — that is the advantage of the join over the subquery approach.