learn deep. ship safe.Practice testsFollow ByteMunSubscribe

easyJoinsSQL

Employees Earning More Than Their Manager

Use a self-join to find employees who are paid more than their own manager: the standard SQL interview question on joining a table to itself.

SQL

The table employees(id, name, salary, manager_id, dept_id) holds every employee. manager_id is the id of the employee's manager, or NULL for someone with no manager.

Write a query that returns the name of every employee whose salary is strictly greater than their manager's salary. Return one column, name, in any order.

Examples

Example 1

Input: employees (id, name, salary, manager_id):
(1, Mira, 9000, NULL)
(2, Ravi, 9500, 1)
(3, Tara, 7000, 1)
Output: name
Ravi
Why: Ravi earns 9500 and his manager Mira earns 9000. Tara earns less than Mira, and Mira has no manager.

Constraints

  • manager_id is NULL for employees without a manager.
  • Equal salaries do not count: the employee must earn strictly more.
  • dept_id can be ignored for this problem.

Hints

Hint 1

You need each employee and their manager on the same row.

Hint 2

Join employees to itself with two aliases, e.g. e for the employee and m for the manager.

Hint 3

An INNER JOIN on e.manager_id = m.id naturally drops employees without a manager.

Solution

Show the solution (try it yourself first)

Approach: self-join

Treat the table as two copies: e is the employee and m is the manager. Joining on e.manager_id = m.id puts each employee next to their manager, and the WHERE clause keeps the rows where the employee earns more.

SELECT e.name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

The inner join drops top-level employees (their manager_id is NULL, which matches nothing), which is what we want: they have no manager to out-earn. A correlated subquery, WHERE salary > (SELECT salary FROM employees WHERE id = e.manager_id), gives the same answer.

Complexity

The join uses the primary key on id, so it is one pass over employees with an index lookup each: O(n log n), effectively linear.

Found a mistake in this problem, its tests or the solution?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table employees
idnamesalarymanager_iddept_id
1Mira9000NULL1
2Ravi950011
3Tara700011
Expected (1 row)
Ravi
Case 2
Table employees
idnamesalarymanager_iddept_id
1Ana5000NULL1
2Bo500011
3Cy600021
4Di400031
Expected (1 row)
Cy

Run checks these 2 samples. Submit checks all 5 tests, including hidden ones.

  1. easy
  2. Highest Paid Employee in Each DepartmentJoins · GROUP BY and aggregates · Window functions · SQL
    medium
  3. Find Duplicate EmailsGROUP BY and aggregates · SQL
    easy
  4. Daily Sales Running TotalWindow functions · GROUP BY and aggregates · SQL
    medium