[240531] SQL 코딩테스트 문제풀이

1. Binary Tree Nodes

https://www.hackerrank.com/challenges/binary-search-tree-1/problem?isFullScreen=true

Binary Tree Nodes | HackerRank

WITH LEAF_TF AS ( SELECT N
FROM BST
WHERE N IN (SELECT DISTINCT(P) 
           FROM BST
           WHERE P is not null ) and P is not NULL )
           
SELECT BST.N, 
    CASE WHEN BST.P IS NULL THEN 'Root'
    WHEN LEAF_TF.N IS NULL and BST.P IS NOT NULL THEN 'Leaf'
    ELSE 'Inner' END 
FROM BST LEFT JOIN LEAF_TF
ON BST.N = LEAF_TF.N
ORDER BY BST.N

 

---

 

2. New Companies ( ⭐️ 복습하기 ) 

• LEFT JOIN 연쇄작업으로 테이블 이어붙이기
• 테이블의 갯수 많음 주의 ! 

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

New Companies | HackerRank

SELECT  C.company_code, 
    C.founder,
    COUNT(DISTINCT(LM.lead_manager_code)),
    COUNT(DISTINCT(SM.senior_manager_code)),
    COUNT(DISTINCT(M.manager_code)),
    COUNT(DISTINCT(E.employee_code ))
FROM Company as C
    LEFT JOIN Lead_Manager as LM ON LM.company_code = C.company_code
    LEFT JOIN Senior_Manager as SM on SM.lead_manager_code = LM.lead_manager_code
    LEFT JOIN Manager as M on SM.senior_manager_code = M.senior_manager_code
    LEFT JOIN Employee as E on E.manager_code = M.manager_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code