SQL 코드카타_07

SQL 코드카타_Analyze Organization Hierarchy

Analyze Organization Hierarchy

문제 : Analyze Organization Hierarchy

# 목적, 목표 : 조직 계층을 분석하여 계급 레벨과 하위 직원 수, 하위 직원의 임금 예산 (본인 포함) 추출
# 필요한 컬럼 : level, team_size, budget
# 컬럼 조건 : manager_id = employee_id 인 경우
# 사용할 테이블 : Employees
# join key : 없음
# 테이블 조건 : 없음
# 필요한 그룹 : 없음
# 필요한 그룹 조건 : 없음
# 정렬 기준 : level, budget DESC, employee_name
# 주의사항 : 재귀함수 사용

로직

• manager_id = employee_id를 기준으로 level 계산
  • manager_id is null → level = 1
  • manager_id is not null → level + 1
• 직속 부하의 직속 부하 계산
  • manager_id = a.employee_id 일 때
    • manager_id = c.employee_id 로 직속 부하의 직속 부하까지 추출
  • count(*) = team_size
  • sum(salary) + 본인 salary = budget

코드 뜯어보기

# 직속 부하 기준 level 계산
with recursive A as (
    select employee_id, employee_name, salary, 1 as level
    from Employees
    where manager_id is null
    union all
    select e.employee_id, e.employee_name, e.salary, a.level + 1
    from A a left join Employees e on a.employee_id = e.manager_id
    where e.manager_id is not null
),

# 직속 부하의 직속 부하까지 추출하여 team_size, budget 계산
B AS (
    SELECT 
        a.employee_id, 
        a.employee_name, 
        a.level,
        (
            WITH RECURSIVE C AS (
                SELECT e.employee_id, e.employee_name, e.salary
                FROM Employees e
                WHERE e.manager_id = a.employee_id -- 직속 부하 추출
                
                UNION ALL
                
                SELECT e2.employee_id, e2.employee_name, e2.salary
                FROM C c
                JOIN Employees e2 ON e2.manager_id = c.employee_id -- 직속 부하 추출한 것에 대한 직속 부하 추가로 join
            )
            SELECT COUNT(*)
            FROM C
        ) AS team_size,
        (
            WITH RECURSIVE C AS (
                SELECT e.employee_id, e.employee_name, e.salary
                FROM Employees e
                WHERE e.manager_id = a.employee_id
                
                UNION ALL
                
                SELECT e2.employee_id, e2.employee_name, e2.salary
                FROM C c
                JOIN Employees e2 ON e2.manager_id = c.employee_id
            )
            SELECT coalesce(SUM(salary),0)  -- 직속 부하가 없는 경우 값이 null로 찍히기 때문
            FROM C
        ) + a.salary AS budget
    FROM A a
)

# 정렬 조건에 따라 정렬하기
select *
from B
order by level, budget DESC, employee_name;

새로 알게된 점

• 재귀 CTE에서 a.employee_id = e.manager_id를 기준으로 JOIN한 경우,
  • a.employee_id를 기준으로 +1이 되어 level이 산정됨
• 재귀 CTE에서 JOIN으로 직속 부하의 직속 부하 추출이 가능하다는 점