Employees Earning More Than Their Managers - Leetcode Solution

Problem Statement:

The Employee table holds all employees including their managers. Each employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager. +----------+ | Employee | +----------+ | Joe | +----------+

Solution:

To solve this problem, we need to perform a self-join on the Employee table. We will join the table with itself using the ManagerId column to match employees with their managers.

The join condition will be that the employee’s ManagerId column matches with the manager’s Id column. Also, the employee’s Salary must be greater than their manager’s Salary.

SELECT employee.Name as Employee FROM Employee employee JOIN Employee manager ON employee.ManagerId = manager.Id WHERE employee.Salary > manager.Salary;

Explanation:

We first specify the columns we want to select, which in this case is the employee’s Name.

Next, we perform a join on the Employee table, joining the table with itself using the ManagerId column to match employees with their managers.

We then specify the join condition, which is that the employee’s ManagerId column matches with the manager’s Id column. Also, the employee’s Salary must be greater than their manager’s Salary.

Finally, we use the WHERE clause to filter the result set based on the condition that the employee’s salary is greater than their manager’s salary.

1