181 Employees Earning More Than Their Managers

Description

Table: Employee

• id is the primary key (column with unique values) for this table.
• Each row of this table indicates the ID of an employee, their name, salary, and managerId-the ID of their manager.

Problem Statement

Write a solution to find the employees who earn more than their managers. Return the result table in any order. The result format is in the following example.

Example 1:

Input:

• Employee table:

Output:

Explanation: Joe is the only employee who earns more than his manager.

Solution

The problem can be divided into two main parts.

1. Find managers for each employee.
2. Find employees who has salary greater than their manager.

In order to find managers for each employee, you can perform self join based on employee.managerId = manager.id.

1SELECT e.name Employee e
2    JOIN Employee m
3    ON e.managerId = m.id;

The second part of the problem states that you need employees for whom the salary is greater than their manager’s salary. You can do this by comparing e.salary > m.salary.

Hence, the problem can be solved using below SQL query.

1SELECT
2    e.name Employee FROM Employee e
3    JOIN Employee m
4    ON e.managerId = m.id
5    WHERE e.salary > m.salary;