How do you approach SQL: Employees Earning More Than Their Manager (Self Join)?

Self-join the Employee table to itself: alias one as e (employee), one as m (manager).

What are common mistakes on SQL: Employees Earning More Than Their Manager (Self Join)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Employees Earning More Than Their Manager (Self Join)

EasyDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. (LeetCode classic 181.) Given: ``` Employee(id, name, salary, manager_id) -- manager_id is nullable, FKs Employee.id ``` Return the names of employees whose salary is **strictly greater than** their direct manager's salary. Order alphabetically. **For the auto-grader:** implement `employeesEarningMoreThanManager(employees)`.

Example 1

Input: employees = [{id:1,name:"Joe",salary:70000,manager_id:3},{id:2,name:"Henry",salary:80000,manager_id:4},{id:3,name:"Sam",salary:60000,manager_id:null},{id:4,name:"Max",salary:90000,manager_id:null}]

Output: ["Joe"]

Explanation: Joe earns 70k, manager Sam earns 60k, so Joe earns more. Henry earns 80k but Max earns 90k.

Constraints

- 0 <= employees.length <= 10^4 - manager_id may be null (the CEO). - Salaries are non-negative integers.

SQL: Employees Earning More Than Their Manager (Self Join): approach, complexity and pitfalls

An easy sql problem, graded against 4 test cases (2 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

Self-join the Employee table to itself: alias one as `e` (employee), one as `m` (manager).
Match `e.manager_id = m.id`, then filter where `e.salary > m.salary`.

The optimal approach

Show the approach: Canonical SQL:

```sql

SELECT e.name

FROM Employee e

JOIN Employee m ON e.manager_id = m.id

WHERE e.salary > m.salary

ORDER BY e.name;

```

A self-join treats the same physical table as two logical relations. The `INNER JOIN` skips employees with NULL manager_id (the CEO can't earn more than nobody). Comparing salaries on the joined row gives the answer.

Common interviewer follow-up: "What if managers are several layers up?" - that's a recursive CTE problem (see problem 134).

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.