SQL | Binary Tree Nodes, New Companies

Binary Tree Nodes

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

Binary Tree Nodes | HackerRank

---

- P가 N의 부모

- P가 NULL값이면 해당하는 N은 Root, P값에 해당하는 N값이 있으면 Inner, P값에 NULL도 없고 값도 없으면 Leaf

SELECT 
    N,   
    CASE WHEN P IS NULL THEN 'Root'
         WHEN N IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Inner'
         ELSE 'Leaf' END
FROM BST
ORDER BY 1

---

New Companies

https://www.hackerrank.com/challenges/the-company/submissions

New Companies | HackerRank

---

- 회사코드, 설립자 이름, 리드 매니저 총수, 시니어 매니저 총수, 매니저 총수, 직원 총수 조회

SELECT DISTINCT
    c.company_code,
    c.founder,
    COUNT(DISTINCT e.lead_manager_code),
    COUNT(DISTINCT e.senior_manager_code),
    COUNT(DISTINCT e.manager_code),
    COUNT(DISTINCT e.employee_code)
FROM Company c JOIN Employee e ON c.company_code = e.company_code
GROUP BY 1,2