181. Employees Earning More Than Their Managers

Employees Earning More Than Their Managers

Problem Description

SQL Schema

Create table If Not Exists Employee (id int, name varchar(255), salary int, managerId int)
Truncate table Employee
insert into Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')
insert into Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')
insert into Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+

id is the primary key column for this table.
Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager. 

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+

Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+

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

Approach 1) Left outer join, faster than where.

# Write your MySQL query statement below
select
    a.Name as Employee
from Employee a
left join
    Employee b
on
    a.ManagerID = b.Id
where
    a.Salary > b.Salary

Approach 2) Determine the judgment condition by a query. This method is relatively slow. After two queries, two sessions are used.

# Write your MySQL query statement below
select
    name as Employee
from
    Employee a
where
    a.Salary > (
        select
            b.Salary
        from
            Employee b
        where
            a.ManagerId = b.Id
    );

Conclusion

That’s all folks! In this post, we solved LeetCode problem #181. Employees Earning More Than Their Managers

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to collect & present the most important points to consider when improving Data structure and logic, feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!