[HackerRank] SQL 문제 풀이 - Medium 레벨 (1)

•  개요
  • SQL 쿼리 작성 능력 함양을 위해 Medium 레벨 문제 풀이를 진행함
  • 본문의 쿼리들은 MySQL 기반으로 작성함

---

• 출처
  • https://www.hackerrank.com/challenges/binary-search-tree-1/problem
• 제목
  • Binary Tree Nodes
• 문제
  • N (nodes) 와 해당 노드들의 관계를 나타낸 P (parents) 컬럼으로 구성된 테이블로부터 전체 노드들을 [Root, Inner, Leaf] 로 분류
• 풀이

SELECT
    N,
    CASE WHEN P IS NULL THEN 'Root'
    WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner'
    ELSE 'Leaf' END AS res
FROM
    BST
ORDER BY
    N;

---

• 출처
  • https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true
• 제목
  • New Companies
• 문제
  • 회사별로 company_code, 창업자 이름, Lead Manager 수, Senior Manager 수, Manager 수, Employee 수를 출력
  • 각 테이블에는 중복 레코드가 있을 수 있으므로, 직급별 인원 수를 셀 때는 중복을 제거해야 함.
  • 결과는 company_code 기준 오름차순으로 정렬하되, company_code는 문자열이므로 숫자 정렬이 아니라 문자열 정렬 기준을 따름.
• 풀이

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT lm.lead_manager_code) AS total_num_lead_managers,
    COUNT(DISTINCT sm.senior_manager_code) AS total_num_senior_managers,
    COUNT(DISTINCT m.manager_code) AS total_num_managers,
    COUNT(DISTINCT e.employee_code) AS total_num_employees
FROM company c
JOIN Lead_Manager lm ON c.company_code = lm.company_code
JOIN Senior_Manager sm ON lm.lead_manager_code = sm.lead_manager_code
JOIN Manager m ON sm.senior_manager_code = m.senior_manager_code
JOIN Employee e ON m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

• 노트
  • gpt 에게 효율화 버전을 요청했음.
    • 모든 테이블에 이미 company_code가 들어 있기 때문에 각 계층을 끝까지 연쇄 조인하지 않아도 회사별 개수 집계가 가능.
      • db 구성 똑바로 안보고 company_code 없다고 생각했음
      • 물론 있었다고 한들 join 컬럼만 바꾸고 먼저 집계 후 join 할 생각은 못했을 것 같다..
    • 내가 작성한 쿼리도 정답은 맞지만, 하위 테이블로 갈수록 행이 불어나므로 중간 조인 결과가 커질 수 있음.
    • 반면 이 문제는 각 테이블을 회사 단위로 먼저 집계한 뒤 Company와만 조인하면 충분함.
  • 첫째, 불필요한 계층 조인을 제거
    • 문제의 출력은 “회사별 각 직급 코드의 distinct 개수”이지, 계층 무결성을 검증하는 것이 아님.
    • 스키마상 각 테이블에 이미 company_code가 있으므로 회사별 집계만 하면 됨.
  • 둘째, 중간 결과 폭증을 줄일 수 있음
    • 내가 작성한 쿼리는 예를 들어 한 회사에 senior manager, manager, employee가 많을수록 조인 결과가 곱처럼 불어날 수 있음.
    • 반면 gpt 방식은 각 테이블에서 GROUP BY company_code로 먼저 압축한 뒤 작은 결과끼리만 조인함.
  • 셋째, 의도가 더 명확
    • “회사별 lead manager 수”, “회사별 employee 수”를 각각 독립적으로 세고 마지막에 합치는 구조라서 문제 요구사항과 더 직접적으로 대응함
  • 항상 더 빠르다고는 장담할 수 없으나 아래 쿼리가 더 가볍고 읽기 쉬운 쿼리라고 함

SELECT
    c.company_code,
    c.founder,
    COALESCE(lm.total_num_lead_managers, 0)   AS total_num_lead_managers,
    COALESCE(sm.total_num_senior_managers, 0) AS total_num_senior_managers,
    COALESCE(m.total_num_managers, 0)         AS total_num_managers,
    COALESCE(e.total_num_employees, 0)        AS total_num_employees
FROM Company c
LEFT JOIN (
    SELECT
        company_code,
        COUNT(DISTINCT lead_manager_code) AS total_num_lead_managers
    FROM Lead_Manager
    GROUP BY company_code
) lm
    ON c.company_code = lm.company_code
LEFT JOIN (
    SELECT
        company_code,
        COUNT(DISTINCT senior_manager_code) AS total_num_senior_managers
    FROM Senior_Manager
    GROUP BY company_code
) sm
    ON c.company_code = sm.company_code
LEFT JOIN (
    SELECT
        company_code,
        COUNT(DISTINCT manager_code) AS total_num_managers
    FROM Manager
    GROUP BY company_code
) m
    ON c.company_code = m.company_code
LEFT JOIN (
    SELECT
        company_code,
        COUNT(DISTINCT employee_code) AS total_num_employees
    FROM Employee
    GROUP BY company_code
) e
    ON c.company_code = e.company_code
ORDER BY c.company_code;

---

• 출처
  • https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true
• 제목
  • Weather Observation Station 20
• 문제
  • STATION 테이블에서 북위 값인 LAT_N의 중앙값을 구하라.
  • 중앙값은 전체 데이터를 정렬했을 때 낮은 절반과 높은 절반을 나누는 값.
  • 최종 결과는 문제에서 요구한 소수 자릿수에 맞춰 반올림하여 출력해야 함.
• 풀이

WITH lat_rows AS
(SELECT
    LAT_N,
    ROW_NUMBER() OVER (ORDER BY LAT_N) AS row_num
FROM STATION),
minmax_rows AS (
SELECT
    MAX(row_num) AS max_row,
    MIN(row_num) AS min_row
FROM lat_rows),
mid_idx AS (
SELECT
    CASE WHEN max_row % 2 = 0 THEN ((max_row/2 + 1) + (max_row/2 -1)) /2
    WHEN max_row % 2 = 1 THEN (max_row + min_row) / 2 END AS idx
FROM minmax_rows)

SELECT ROUND(LAT_N, 4)
FROM lat_rows
WHERE row_num = (SELECT idx FROM mid_idx)

• 노트
  • HackerRank UI는 분명 깔끔해서 좋긴 한데 묘하게 읽기 싫고.. 묘하게 더 어려운 것 같고.. 묘하게 집중 안되는 느낌이 있다...
  • MySQL은 MEDIAN()이 없다니 !!
  • 구글링하다가 ROW_NUMBER() 로 풀면 된다는 힌트를 얻었음

---

• 출처
  • https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true
• 제목
  • The Report
• 문제
  • 학생의 이름, 성적 등급, 점수를 포함하는 리포트를 출력하라.
  • 학생의 점수를 Grades 테이블의 점수 구간과 매칭해 등급을 계산해야 함.
  • 등급이 8 이상인 학생은 이름을 그대로 출력하고, 등급이 8 미만인 학생은 이름 대신 NULL을 출력.
  • 정렬은 먼저 등급 내림차순. 등급이 8~10인 학생들이 같은 등급이면 이름을 알파벳순으로 정렬하고, 등급이 1~7인 학생들이 같은 등급이면 점수를 오름차순으로 정렬.
• 풀이

SELECT
    CASE WHEN Grade < 8 THEN NULL ELSE Name END AS Name,
    Grade,
    Marks
FROM (SELECT
    Name,
    Marks,
    CASE WHEN (Min_Mark <= Marks) AND (Marks <= Max_Mark) THEN Grade ELSE NULL END AS Grade
FROM Students s, Grades g
WHERE (CASE WHEN (Min_Mark <= Marks) AND (Marks <= Max_Mark) THEN Grade ELSE NULL END) IS NOT NULL) a
ORDER BY
    Grade DESC,
    CASE WHEN Grade >= 8 THEN Name END ASC,
    CASE WHEN Grade < 8 THEN Marks END ASC

• 노트
  • 문제를 잘못 이해해서 정렬 기준을 잘못 잡고 시작했음; grade (10-8) 이랑 lower than 8 로 구분해놨넹 ..
    • 문제: 등급(10-8) > 이름 & 등급(7-1) > 성적
    • 나: 등급(10-8) > 이름 > 성적 & 등급(7-1) > 성적
  • 그래서 구글링하다가 ORDER BY에서 CASE WHEN을 최종 풀이처럼 사용할 수 있음을 알게 됨
    • CASE WHEN 내부에 ASC / DESC 사용 불가하다고 함
  • 부등호도 빼먹음
    • 정답 풀이처럼 컬럼에 BETWEEN 조건 달아서 JOIN 할 수 있음을 알게 됨

# gpt 정답
SELECT
    CASE WHEN g.Grade < 8 THEN NULL ELSE s.Name END AS Name,
    g.Grade,
    s.Marks
FROM Students s
JOIN Grades g
    ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark
ORDER BY
    g.Grade DESC,
    CASE WHEN g.Grade >= 8 THEN s.Name END,
    CASE WHEN g.Grade < 8 THEN s.Marks END;

---

• 제목
  • Placements
• 문제
  • 각 학생과 그 학생의 가장 친한 친구, 그리고 두 사람의 연봉 제안 금액을 비교하라.
  • 학생 본인보다 가장 친한 친구가 더 높은 연봉을 제안받은 경우에만 해당 학생의 이름을 출력.
  • 출력 결과는 친구가 제안받은 연봉 금액 기준으로 오름차순 정렬한다. 문제에서는 모든 학생의 연봉 제안 금액이 서로 다르다고 가정.
• 풀이

WITH Stdnt_sal AS (
    SELECT
        s.ID,
        s.Name,
        p.Salary
    FROM Packages p
    JOIN Students s ON p.ID = s.ID
), Frnd_sal AS (
    SELECT
        f.ID AS ID,
        f.Friend_ID AS F_ID,
        s.Name AS F_Name,
        p.Salary AS F_Salary
    FROM Friends f
    LEFT OUTER JOIN Students s ON s.ID = f.Friend_ID
    LEFT OUTER JOIN Packages p ON f.Friend_ID = p.ID
)

SELECT
    -- ss.ID,
    ss.Name
    -- ss.Salary,
    -- fs.F_ID,
    -- fs.F_Salary,
    -- fs.F_Name
FROM 
    Stdnt_sal ss
JOIN Frnd_sal fs ON ss.Salary < fs.F_Salary AND ss.ID = fs.ID
ORDER BY fs.F_Salary

• 노트
  • 친구 연봉으로 정렬하라고 해서 DESC로 했다가 gpt한테 물어봐서 ASC인 것을 이해했다.
  • 샘플을 보니 '나'보다 친구 연봉이 높은 경우를 필터링하고 친구 연봉 따라 오름차순 정렬이었다.
  • 언제쯤 샘플을 보지 않고도 턱턱 쿼리를 짤 수 있을까 !!!!!!!!
  • 아니 다시 느끼지만 Hackerrank 문제 UI ? 너무 답답하다..
  • 테이블이 이미지로 들어가있어서 크기도 중구난방이고 그래서 문제 설명이 문단으로 분리돼있을 때 확인하기가 힘들다 악악

---