117번

문제

가장 많은 친구를 가진 사람과 그 친구 수를 찾는 풀이를 작성하시오.
테스트 케이스는 오직 한 사람만이 가장 많은 친구를 가지도록 생성된다.
결과 형식은 다음 예시와 같다.

Input

requester_id	accepter_id	accept_date
1	2	2016/06/03
1	3	2016/06/08
2	3	2016/06/08
3	4	2016/06/09

Output

id	num
3	3

---

문제 상에는 친구라고 되어있긴 하지만, 결국에는 requester_id와 accepter_id를 합쳐서 가장 많은 빈도수를 나타내는 사람의 id와 건 수를 출력하면 된다.

두 컬럼을 UNION ALL로 한 줄로 만든 다음에 거기서 가장 높은 빈도의 id를 하나만 출력하도록 작성했다.

SELECT requester_id AS id, COUNT(*) AS num
FROM (
    SELECT requester_id
    FROM RequestAccepted
    UNION ALL
    SELECT accepter_id
    FROM RequestAccepted
) AS a
GROUP BY requester_id
ORDER BY 2 DESC LIMIT 1;

---

118번

2016년 tiv_2016 총 투자 금액 합계를 보고하는 풀이를 작성하시오.
조건:
• tiv_2015 값이 다른 한 명 이상의 보험가입자와 동일한 사람
• 다른 어떤 보험가입자와도 같은 도시에 있지 않은 사람 (즉, (lat, lon) 쌍이 유일해야 함)

tiv_2016은 소수점 둘째 자리까지 반올림한다.
결과 형식은 다음 예시와 같다.

Input

pid	tiv_2015	tiv_2016	lat	lon
1	10	5	10	10
2	20	20	20	20
3	10	30	20	20
4	10	40	40	40

Output

tiv_2016
45.00

---

tiv_2015는 중복이 있어야 하며 (COUNT > 1) lat과 lon의 조합은 중복 불가 (lat과 lon을 CONCAT으로 병합한 뒤에 COUNT = 1 이어야 함)
윈도우 함수로 각각의 카운트를 PARTITON BY로 구하고 그 결과로 나오는 tiv_2016을 ROUND로 반올림하면 된다.

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM
(
    SELECT 
        COUNT(tiv_2015) OVER(PARTITION BY tiv_2015) AS cnt_2015,
        tiv_2016, 
        COUNT(CONCAT(lat, lon)) OVER(PARTITION BY CONCAT(lat, lon)) AS loc
    FROM Insurance
) AS a
WHERE cnt_2015 > 1 AND loc = 1;

---

119번

문제

한 회사의 임원들은 각 부서에서 누가 가장 많은 급여를 받는지 확인하고 싶어합니다.
각 부서에서 상위 세 개의 유일한 급여(top 3 unique salaries)를 받는 직원이 해당 부서의 고소득자(high earner)입니다.

각 부서의 고소득자를 찾는 쿼리를 작성하시오.
결과 테이블은 순서 상관없이 반환하면 됩니다.
결과 형식은 다음 예시와 같습니다.

Employee

id	name	salary	departmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department

id	name
1	IT
2	Sales

Ouput

Department	Employee	Salary
IT	Max	90000
IT	Joe	85000
IT	Randy	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

---

결과 예시에 같은 Salary여도 같은 순위를 매긴다는 것을 힌트로 DENSE_RANK를 사용해야 한다는 것을 알 수 있다.
DENSE_RANK와 WITH를 사용해서 빠르게 풀었다.

WITH sal_rnk AS(
    SELECT 
        d.name AS Department, 
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER(PARTITION BY d.name ORDER BY e.salary DESC) AS rnk
    FROM Employee e
    JOIN Department d
    ON e.departmentId = d.id
    ORDER BY 1, 4
)
SELECT Department, Employee, Salary
FROM sal_rnk
WHERE rnk <=3;