[LeetCode] 1789. Primary Department for Each Employee - SQL

링크: https://leetcode.com/problems/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.

---

문제풀이

목표: 모든 직원의 primary_department를 보고하는 솔루션 작성.

• 직원은 여러 부서에 속할 수 있다.
• 직원이 하나의 부서에만 속해 있는 경우 그들의 primary column 은 ‘N’
• 하나의 부서에 소속된 직원의 경우 해당 부서만 보고.

최종코드

SELECT 
    employee_id,
    department_id
FROM Employee
WHERE primary_flag = 'Y' or
    employee_id IN (
        SELECT employee_id
        FROM Employee
        GROUP BY employee_id
        HAVING COUNT(employee_id) = 1
        );

• Runtime : 452ms

설명

FROM, WHERE 절

FROM Employee
WHERE primary_flag = 'Y' or
    employee_id IN (
        SELECT employee_id
        FROM Employee
        GROUP BY employee_id
        HAVING COUNT(employee_id) = 1
        );

• Employee 테이블에서
• primary_flag 가 Y 이거나,
• employee_id 가 서브쿼리의 결과에 있는 것만 필터링한다.
  • 서브쿼리:
    • Employee 테이블에서
    • employee_id 를 기준으로 그룹화하고
    • 오직 하나의 부서에만 속해 있는 직원을 조회한다.

SELECT 절

SELECT 
    employee_id,
    department_id

• employee_id,
• department_id 조회하면 끝!

다른 사람의 풀이

SELECT employee_id, department_id
FROM Employee 
WHERE primary_flag = 'Y'

UNION

SELECT employee_id, department_id
FROM Employee 
GROUP BY employee_id
HAVING COUNT(employee_id) = 1
ORDER BY employee_id;

• Runtime: 534ms
• UNION 없이 푼 내 방식이 80ms 정도 더 빠르다.