181. Employees Earning More Than Their Managers – Leetcode Solutions

Description

able: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+
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 the ID of their manager.

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.

Examples:

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.

Solution in Pandas

To solve this problem using Pandas, we need to identify employees who earn more than their managers.

Step-by-step explanation and the corresponding code:

1. Merge the Employee DataFrame with itself:
   • We join the DataFrame on the managerId and id columns to compare the salary of each employee with the salary of their manager.
2. Filter the DataFrame:
   • After merging, filter the rows where the employee’s salary is greater than the manager’s salary.
3. Select the required column:
   • Extract the names of the employees who meet the condition.

import pandas as pd

def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
    # Merge the DataFrame with itself to compare employee salaries with their manager's salary
    merged_df = employee.merge(employee, left_on='managerId', right_on='id', suffixes=('', '_manager'))
    
    # Filter the rows where the employee's salary is greater than their manager's salary
    result_df = merged_df[merged_df['salary'] > merged_df['salary_manager']]
    
    # Select only the name column of the employees who earn more than their managers
    result_df = result_df[['name']]
    
    # Rename the column to 'Employee' to match the desired output format
    result_df.columns = ['Employee']
    
    return result_df

Explanation of the code:

1. Merging DataFrames:
   • employee.merge(employee, left_on='managerId', right_on='id', suffixes=('', '_manager')) joins the DataFrame with itself. left_on='managerId' and right_on='id' ensure that we are matching each employee with their respective manager. The suffixes parameter helps distinguish between employee columns and manager columns in the merged DataFrame.
2. Filtering:
   • merged_df[merged_df['salary'] > merged_df['salary_manager']] filters out the rows where the employee’s salary is greater than the manager’s salary.
3. Selecting and Renaming Columns:
   • result_df[['name']] selects the ‘name’ column of the employees who meet the condition.
   • result_df.columns = ['Employee'] renames the column to ‘Employee’ to match the required output format.

This code assumes that the input DataFrame employee is already provided and is in the correct format. The function returns a DataFrame containing the names of the employees who earn more than their managers.

Solution in MySQL

SELECT e1.name AS Employee
FROM Employee e1
JOIN Employee e2 ON e1.managerId = e2.id
WHERE e1.salary > e2.salary;

Solution in PostgreSQL

SELECT e1.name AS Employee
FROM Employee e1
JOIN Employee e2 ON e1.managerId = e2.id
WHERE e1.salary > e2.salary;