181. Employees Earning More Than Their Managers

Table: 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.

 

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.

 

 

문제 : 아래 표에서 관리자 보다 더 많은 돈을 버는 직원을 찾는 솔루션을 작성해라.

1. 직원 아이디 안에 관리자의 아이디 또한 포함되어 있다.

2. 관리자와 직원의 급여 정보 비교를 하기 위해서는 wide 폼으로 바꿔줘야 한다.
즉 병합해야 한다.
3. 이후 두 값을 빼서 높은 급여가 있는 직원 쪽의 이름을 가져온다.

merge : left_on, right_on, how , suffixes 인수 이용 / SQL과 비슷
rename : 행기준 이름 변경

 

import pandas as pd

def find_employees(employee: pd.DataFrame) -> pd.DataFrame:
    df = employee.merge(employee, left_on = 'managerId', right_on = 'id', how = 'inner', suffixes = ['_ex','_manager'])
    return df.loc[df['salary_ex'] - df['salary_manager'] > 0 , ['name_ex']].rename({'name_ex' : 'Employee'}, axis = 1)