LeetCode - 3482. Analyze Organization Hierarchy (Oracle)

Hard - SQL, SubQuery, 계층적 질의

RunTime : 390 ms

문제

Write a solution to analyze the organizational hierarchy and answer the following:

1. Hierarchy Levels: For each employee, determine their level in the organization (CEO is level 1, employees reporting directly to the CEO are level 2, and so on).
2. Team Size: For each employee who is a manager, count the total number of employees under them (direct and indirect reports).
3. Salary Budget: For each manager, calculate the total salary budget they control (sum of salaries of all employees under them, including indirect reports, plus their own salary).

Return the result table ordered by the result ordered by level in ascending order, then by budget in descending order, and finally by employee_name in ascending order.

The result format is in the following example.

풀이

1. LEVEL을 명시적으로 출력
   : START WITH을 통해 시작 ROW 지정,
   CONNECT BY로 ROW 간 계층 조건 지정

2. 스칼라 서브쿼리(SELECT 내의 서브쿼리)를 통해
   현재 자신의 값을 시작 기준으로 하는 ROW의 갯수 카운팅

3. 자신의 SALARY + NVL(서브쿼리)를 통해
   현재 자신의 salary값 + 0 or 하위 계층의 sum(salary) 계산

select  employee_id,
        employee_name,
        level,
        (
            select  count(*)
            from    employees e2
            connect by prior employee_id = manager_id
            start with e2.manager_id = e1.employee_id
        ) as team_size,
        salary + nvl((
            select  sum(salary)
            from    employees e2
            connect by prior employee_id = manager_id
            start with e2.manager_id = e1.employee_id
        ), 0) as budget
from employees e1
connect by prior employee_id = manager_id
start with manager_id is null
order by level, budget desc, employee_name;

Hard는 어렵긴 한데
이정도는 업무로도 작성하는 것 같다.