1978. Employees Whose Manager Left the Company

https://leetcode.com/problems/employees-whose-manager-left-the-company/

 

 

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

 

내용 요약:

매니저가 퇴사하면, Employess 목록에서 사라지지만, 다른 직원의 매니저로서 기존 설정된 기록은 남아 있다.

퇴사하면 기록이 사라진다는 말 때문에, "NULL" 로 표현된 부분을 오해하기가 너무 쉽다.

("NULL"은 퇴사와 관련이 없음) 

 

 

 

Table: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+
In SQL, employee_id is the primary key for this table.
This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null). 

 

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

Return the result table ordered by employee_id.

The result format is in the following example.

 

Example 1:

Input:  
Employees table:
+-------------+-----------+------------+--------+
| employee_id | name      | manager_id | salary |
+-------------+-----------+------------+--------+
| 3           | Mila      | 9          | 60301  |
| 12          | Antonella | null       | 31000  |
| 13          | Emery     | null       | 67084  |
| 1           | Kalel     | 11         | 21241  |
| 9           | Mikaela   | null       | 50937  |
| 11          | Joziah    | 6          | 28485  |
+-------------+-----------+------------+--------+
Output: 
+-------------+
| employee_id |
+-------------+
| 11          |
+-------------+

 

 

 

 

1. 수입이 3만보다 작은 직원에 매칭되는 manager_id 선택

 

2. 선택된 manager_id 중에서, "employee" 테이블의 employee_id에 포함되어 있지 않은 값을 SELECT 문으로 선택

(퇴사한 매니저는 employee_id에 존재하지 않음)

 

3. 이 과정에서 Employees 테이블을 다시 이용하므로 salary 값이 3만보다 큰 경우가 생길 수 있음.

다시 WHERE 조건에 추가 > (AND Employees.salary < 30000) 

 

4. 아래 작성한 쿼리 처림 카타시안 곱은 사용하면 안됨 > 쿼리성능 저하

 

 

예외케이스 

----------------------------------------------------------------

| employee_id | name    | manager_id | salary |
| ----------- | ------- | ---------- | ------ |
| 9           | Kamiyah | 2          | 65859  |
| 14          | Russell | 10         | 86740  |
| 11          | Roger   | 7          | 85366  |
| 15          | Alyson  | null       | 91743  |
| 17          | Elyse   | 12         | 10115  |
| 3           | Alivia  | null       | 53679  |
| 1           | Reign   | null       | 77731  |
| 7           | Brooks  | 11         | 89386  |
| 8           | Ryland  | 12         | 61431  |
| 13          | Charlee | null       | 41084  |

----------------------------------------------------------------  

 

 

 

# salary가 3만 보다 작은 employee의 manager_id 선택
WITH cond1 AS (
                SELECT manager_id
                FROM Employees 
                WHERE salary < 30000
), 

# cond1 에서 선택한 manager_id 가 Employees 테이블의 employee_id에 존재하지 않는, cond1의 manager_id 선택
cond2 AS(
            SELECT DISTINCT cond1.manager_id
            FROM Employees, cond1                       # 카타시안 곱 사용금지
            WHERE cond1.manager_id NOT IN (SELECT employee_id FROM Employees)
)

# 조건에 맞는 manager_id 만을 최종 선택 후, Employees 테이블에서 해당 manager_id를 가진 employee_id를 선택
# 이 과정에서 Employees 테이블을 다시 이용하므로 salary 값이 3만보다 큰 경우가 생길 수 있어 다시 WHERE 조건에 추가

SELECT Employees.employee_id
FROM Employees
     INNER JOIN cond2 ON Employees.manager_id = cond2.manager_id
WHERE Employees.manager_id = cond2.manager_id
AND Employees.salary < 30000
ORDER BY employee_id