리트코드 : 577. Employee Bonus

리트코드 : 577. Employee Bonus

• https://leetcode.com/problems/employee-bonus/description/
  

---

문제

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| empId       | int     |
| name        | varchar |
| supervisor  | int     |
| salary      | int     |
+-------------+---------+
empId is the column with unique values for this table.
Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.


Table: Bonus

+-------------+------+
| Column Name | Type |
+-------------+------+
| empId       | int  |
| bonus       | int  |
+-------------+------+
empId is the column of unique values for this table.
empId is a foreign key (reference column) to empId from the Employee table.
Each row of this table contains the id of an employee and their respective bonus.


Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input: 
Employee table:
+-------+--------+------------+--------+
| empId | name   | supervisor | salary |
+-------+--------+------------+--------+
| 3     | Brad   | null       | 4000   |
| 1     | John   | 3          | 1000   |
| 2     | Dan    | 3          | 2000   |
| 4     | Thomas | 3          | 4000   |
+-------+--------+------------+--------+
Bonus table:
+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
Output: 
+------+-------+
| name | bonus |
+------+-------+
| Brad | null  |
| John | null  |
| Dan  | 500   |
+------+-------+

문제 풀이

MySQL

SELECT NAME, BONUS
FROM EMPLOYEE AS E
LEFT JOIN BONUS AS B ON E.EMPID = B.EMPID
WHERE BONUS IS NULL OR BONUS < 1000

• BONUS가 없는 경우는 테이블에 기록되지 않는다.
• 이런 사람들도 조회해야 하므로, LEFT JOIN을 해준다.
• NULL이거나 보너스가 없는 사람들을 조회해주기.

Pandas

import pandas as pd

def employee_bonus(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
    temp = pd.merge(employee,bonus,how='left',on='empId')
    cond = temp[(temp['bonus'].isnull()) | (temp['bonus'] < 1000)]
    return cond[['name','bonus']]

• 같은 방법으로 left join을 통해서 서브 쿼리를 만든다.
• 이후에 조건을 걸어서 값 가져오기.
• 1000이상 데이터들을 drop시켜도 된다.

코멘트

• 기본 조인문제