1789. Primary Department for Each Employee

Table: Employee

+---------------+---------+
| Column Name   |  Type   |
+---------------+---------+
| employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
+---------------+---------+
(employee_id, department_id) is the primary key (combination of columns with unique values)
for this table.
employee_id is the id of the employee.
department_id is the id of the department to which the employee belongs.
primary_flag is an ENUM (category) of type ('Y', 'N'). If the flag is 'Y', the department
is the primary department for the employee. If the flag is 'N', the department is not the primary.
 
Employees can belong to multiple departments. When the employee joins other departments,
they need to decide which department is their primary department. Note that when an employee
belongs to only one department, their primary column is 'N'.
Write a solution to report all the employees with their primary department.
For employees who belong to one department, report their only department.

Return the result table in any order.
The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 1           | 1             | N            |
| 2           | 1             | Y            |
| 2           | 2             | N            |
| 3           | 3             | N            |
| 4           | 2             | N            |
| 4           | 3             | Y            |
| 4           | 4             | N            |
+-------------+---------------+--------------+
Output: 
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
Explanation: 
- The Primary department for employee 1 is 1.
- The Primary department for employee 2 is 1.
- The Primary department for employee 3 is 3.
- The Primary department for employee 4 is 3.

 

 

부서가 1개인 employee는 해당 부서가 바로 primary 부서고, 2개 이상인 부서는 primary_flag가 Y인 부서가 primary 부서다. 부서가 2개 이상인데 flag가 Y인 부서가 없는 경우, primary 부서가 없는 것으로 간주한다.

 

이 조건만 맞춰주면 되겠구나! 생각해서 쿼리를 척척 작성했다.

 

그런데 웬걸, 오답이 나온게 아닌가.

-- 처음 풀이
select employee_id
     , case when (count(department_id) > 1 and primary_flag = 'Y') then department_id
            when count(department_id) = 1 then department_id 
            end as department_id
  from Employee
  group by employee_id
  having department_id is not null;
  
 
-- output
| employee_id | department_id |
| ----------- | ------------- |
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |

-- expected
| employee_id | department_id |
| ----------- | ------------- |
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |

 

 

employee 2의 department가 1로 제대로 나온걸로 봐서, 내 생각대로라면 4번의 department_id는 3이 나와야 하는게 맞는데 아예 출력이 되질 않았다.

그리고, 알아본 결과 원인은 다음과 같았다.

 

 CASE WHEN 문은 그룹의 각 행을 개별적으로 확인하지 않고, 그룹의 첫 번째 행만을 확인한다.

한 그룹에 여러 행이 있더라도 맨 위의 행만을 출력하는, GROUP BY랑 비슷했던 것이다.

 

 

employee 4의 department가 null이 나온 이유는, 풀어서 설명해보면 아래와 같다.

 

1.

  employee id로 그룹화 되어서, employee_id가 4인 그룹엔 다음과 같은 행들이 있다.

+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 4           | 2             | N            |
| 4           | 3             | Y            |
| 4           | 4             | N            |
+-------------+---------------+--------------+

 

 

2.

  select ...... case when (count(department_id) > 1 and primary_flag = 'Y') then department_id ...... end as department_id 구문에 의해, employee 4번의 그룹은 아래와 같은 데이터를 갖게 된다.

| employee_id | department_id |
| ----------- | ------------- |
| 4           | null          |
| 4           | 3             |
| 4           | null          |

 

 

3. 그런데 group by랑 비슷하게 case when 구문 또한, 각 그룹 별로 맨 위의 행만을 표시하므로 결국 남는 데이터는 아래와 같다. 

| employee_id | department_id |
| ----------- | ------------- |
| 4           | null          |

 

 

4. having절 조건에 의해 employee 4가 날아간 것!

 

 

그럼 department id가 제대로 표시되게 하려면 어떻게 바꿔주면 좋을까?

case when 구문을 이렇게 바꿔주면 된다.

case when count(department_id) > 1 then max(case when primary_flag = 'Y' then department_id end)

 

전체 쿼리를 보자.

-- 고친 풀이

select employee_id
     , case when count(department_id) > 1 then max(case when primary_flag = 'Y' then department_id end)
            when count(department_id) = 1 then department_id 
            end as department_id
  from Employee
  group by employee_id
  having department_id is not null;

 

 

- 수정된 case when 구문 해석

1. case when count(department_id) > 1 then ... : count() 함수를 사용하여 employee별 그룹의 행 수를 확인한다(count() 안에 *을 넣어도 상관 없다). 행 수가 수가 1보다 크다면, 그 다음 조건을 확인한다.
2. max(case when primary_flag ='Y' then department_id end) : 행 갯수가 1보다 큰 그룹의 행 중 primary_flag = 'Y'인 행의 department_id를 찾고, max() 함수를 사용하여 이런 행들 중에서 가장 큰 department_id를 반환한다.

  => flag가 Y가 아닌 행의 department_id는 모두 null로 처리했고, flag가 Y인 부서는 하나기 때문에 max()가 아닌 min()을 써도 별 상관은 없다. 다만, flag가 Y인 부서가 여럿이라면 다른 방법을 고민해야 할 것이다.

 

 

** 이 문제는 union을 써서 풀면 더 이해하기 쉽게 풀 수 있을 것 같다!