리드코드(leetcode) 문제풀이 : Employees Whose Manager Left the Company (10/8)

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

Explanation:
The employees with a salary less than $30000 are 1 (Kalel) and 11 (Joziah).
Kalel's manager is employee 11, who is still in the company (Joziah).
Joziah's manager is employee 6, who left the company because there is no row for employee 6 as it was deleted.

처음에는 문제 설명을 제대로 이해를 못하고 output 만 생각하고 쿼리를 짰다.

-- 완전 오답 쿼리
select employee_id
from (
	select employee_id
    from employees
    where salary < 30000
) as leftempl
order by employee_id desc
limit 1;

salary 가 30000 이하에서 employee_id 를 내림차순으로 만들고 상위 하나만 뽑아내는 쿼리로 짰다.

하지만 output을 조회하는 쿼리만 맞았지 테스트케이스에서 걸러지는 완전히 문제 설명과는 다른 오답이었다.

문제를 다시 읽어보고 해석해보았다. 최대한 번역기는 안써보고 풀고 싶었기때문에 문제를 제대로 이해하는데 시간이 걸렸다.

Explanation:
The employees with a salary less than $30000 are 1 (Kalel) and 11 (Joziah).
Kalel's manager is employee 11, who is still in the company (Joziah).
Joziah's manager is employee 6, who left the company because there is no row for employee 6 as it was deleted.

테이블에서 employee_id 가 11 인 Joziah 는 employee_id 가 1 인 Kalel 의 manager 이고, 그 뜻은 Joziah 는 아직 퇴사를 안한 상태이다. 하지만 Joziah 의 manager 인 6번을 가진 employee 는 없기때문에 Joziah 의 manager 는 퇴사한 상태이다.

문제를 제대로 이해하고 나니 기존 쿼리가 완전히 잘못 된 것을 알게 되었다. 테스트 케이스에는 output 의 예시가 더 있어서 이해하는데 도움이 되었다.

-- 정답 쿼리
select employee_id
from employees empl
where salary < 30000
    and manager_id is not null
    and manager_id not in (select employee_id 
                            from employees)
order by employee_id;

• where 절 :
  salary 가 30000 미만의 조건을 처음으로 주고,
  • and manager_id is not null :
    manager_id 가 null 인 컬럼을 제외해 준다.
  • and manager_id not in (select employee_id from employees) :
    manager_id 가 employees 테이블에 없는 경우를 not in 을 사용하여 조건식을 완성해주었다.