[hackerrank][SQL][Intermediate]

 

https://www.hackerrank.com/domains/sql?filters%5Bskills%5D%5B%5D=SQL%20%28Intermediate%29 

Solve SQL Code Challenges

 

intermediate

 

☆Binary Tree Nodes

select N,
    case when P is null then "Root"
         when N not in (select distinct P from BST where P is not null) then "Leaf"
         else "Inner"
    end 
from BST
order by N;

▶ where P is not null (필수) 

 

New Companies

select C.company_code, C.founder, 
    count(distinct L.lead_manager_code), count(distinct S.senior_manager_code), 
    count(distinct M.manager_code), count(distinct E.employee_code)

from Company C 
    left join Lead_Manager L 
        on C.company_code = L.company_code
    left join Senior_Manager S 
        on L.lead_manager_code = S.lead_manager_code
    left join Manager M 
        on S.senior_manager_code = M.senior_manager_code
    left join Employee E 
        on M.manager_code = E.manager_code
        
group by C.company_code, C.founder
order by C.company_code

/* another way */
select c.company_code, c.founder, 
    count(distinct l.lead_manager_code), count(distinct s.senior_manager_code), 
    count(distinct m.manager_code),count(distinct e.employee_code) 
from Company c, Lead_Manager l, Senior_Manager s, Manager m, Employee e 
where c.company_code = l.company_code 
    and l.lead_manager_code=s.lead_manager_code 
    and s.senior_manager_code=m.senior_manager_code 
    and m.manager_code=e.manager_code 
group by c.company_code order by c.company_code;

☆Weather Observation Station 20

select round(AVG(LAT_N),4) MEDIAN
from (select LAT_N, ROW_NUMBER() over(order by LAT_N) rn
      , count(1) over() + 1 cnt
      from STATION) sub
where rn between FLOOR(cnt/2) and CEIL(cnt/2)
;

▶ count(1) over() : 전체 행의 개수 반환