해커랭크 SQL 문제풀이 - Medium ①

해커랭크 sql medium 문제를 풀어봤습니다. 프로그래머스, solvesql에 비하면 medium부터는 난이도가 꽤 올라가는 것 같네요. 더보기를 클릭하시면 각 문제의 풀이를 보실 수 있고, Mysql 기준입니다. 

 

 

- The pads

총 두 가지 쿼리를 작성하는 문제입니다.

1. 명단에 포함된 사람들의 이름과 직업을 '이름(직업 첫글자)' 형태로 나타내고, 이를 알파벳순으로 정렬합니다.

2. 같은 직업을 가진 사람이 몇 명이나 되는지 출력하는 쿼리를 작성합니다.

첫 번째 쿼리 : concat을 사용해 문자열을 이어 붙입니다. 직업 첫 글자를 나타내기 위해 left 함수를 사용합니다.

select concat(name, "(", left(occupation,1), ")")
from OCCUPATIONS
order by name asc

두 번째 쿼리 : 같은 직업을 가진 사람의 수를 세기 위해 group by를 사용합니다.

select concat("There are a total of ", count(OCCUPATION), " ", lower(OCCUPATION), "s.")
from OCCUPATIONS
group by OCCUPATION
order by count(OCCUPATION), occupation

---

- Occupations

이름 | 직업으로 구성된 데이터프레임을 pivot하여 같은 직업을 가진 사람의 이름이 세로로 정렬되도록 합니다. 이때, 다른 직업군과 row number가 다르면 부족한 부분에 null을 대신 적습니다.

 

MySQL은 pivot 함수가 따로 없어서 조금 애를 먹었던 문제입니다.

from절 서브쿼리를 통해 풀 수 있는 문제입니다. 서브쿼리는 아래와 같습니다.

select name, occupation, row_number() over (partition by occupation order by name) name_order 
from occupations

이름, 직업, 직업에 따른 행 번호를 계산합니다.

select max(if(occupation = "Doctor", name, null)) as Doctor, max(if(occupation = "Professor", name, null)) as professor, max(if(occupation = "Singer", name, null)) as singer, max(if(occupation = "Actor", name, null)) as actor
from subquery as row_nm
group by name_order

특정 직업에 해당하면 이름을, 해당하지 않으면 null인 컬럼을 생성합니다 -- 직업 개수만큼 생성

직업에 따른 행번호로 group by 하여 나타냅니다.

---

- Binary Tree Nodes

특정 노드가 root인지 leaf인지 inner인지 판단하는 문제입니다. 계층형 질의 문제인가 했는데, 아니더라고요. 여기서 n은 node, p는 n의 parent입니다.

* inner : leaf도 root도 아닌 경우

root/leaf/inner 여부를 판단하기 위해서 case when 구문을 사용합니다. case when 구문 내에 서브쿼리를 사용하여 n이 p에 포함되는지 확인합니다. 

select n, #n이 p에 포함되어 있고, p가 결측값이 아니라면 n은 inner : 부모가 있고, n 자신도 부모가 되므로 inner
    case when n in (select distinct p from BST) and p is not null then "Inner" 
    #n이 p에 포함되어 있고 p가 결측값이라면 n은 root : n 자신은 부모가 되지만 부모가 없으므로 root
    #n이 p에 포함되어 있지 않다면 n은 leaf : n 자신이 부모가 되지 않으므로 leaf
    when n in (select distinct p from BST) and p is null then "Root" else "Leaf" end as types
from BST
order by n asc

---

- New companies

총 5개의 테이블을 이용해서 회사 코드, 창립자 이름, 리드 매니저의 수, 시니어 매니저의 수, 매니저의 수, 직원의 수를 출력하는 쿼리를 작성하는 문제입니다.

1. 회사 코드 | 창립자 이름
2. 리드 매니저 | 회사 코드
3. 시니어 매니저 | 리드 매니저 | 회사 코드
4. 매니저 | 시니어 매니저 | 리드 매니저 | 회사 코드
5. 직원 | 매니저 | 시니어 매니저 | 리드 매니저 | 회사 코드

테이블이 많아서 조금 복잡해보이지만, 회사 코드를 기준으로 전부 조인해 주시면 됩니다. 창립자 이름은 첫 번째 테이블에만 나와있으므로 left join으로 묶어주시면 됩니다. 

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 c.company_code = s.company_code
left join Manager m on c.company_code = m.company_code
left join Employee e on c.company_code = e.company_code
group by c.company_code, c.founder #회사 코드와 창립자 이름에 따라 group by
order by c.company_code

직급에 따른 직원의 수를 출력할 때는 distinct를 적어주셔야 고유한 수를 계산할 수 있습니다. 

---

- Weather Observation Station 18

두 지점의 좌표를 받아서 맨하탄 거리를 계산하는 문제입니다. 맨하탄 거리 공식은 다음과 같습니다.

$$ Manhattan \ distance = \left|x_{1}-x_{2}\right|+\left|y_{1}-y_{2}\right| $$

abs를 통해 절대값을 구하고 round를 통해 반올림만 해주시면 됩니다.

select round(abs(min(s.lat_n) - max(s_new.lat_n)) + abs(min(s.long_w) - max(s_new.long_w)), 4)
from station s, station s_new

이때, 두 지점의 좌표를 사용하기 위해 동일한 테이블을 서로 다른 alias로 두 번 불러와야 합니다.