[HackerRank] Placements

Q. You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

< Students >

Column	Type
ID	Integer
Name	String

< Friends >

Column	Type
ID	Integer
Friend_ID	Integer

< Packages >

Column	Type
ID	Integer
Salary	Float

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

Sample Input

< Students >

ID	Name
1	Ashley
2	Samantha
3	Julia
4

< Friends >

ID	Friend_ID
1	2
2	3
3	4
4	1

< Packages >

ID	Salary
1	15.20
2	10.06
3	11.55
4	12.12

Sample Output

Samantha
Julia
Scarlet

정답

SELECT sub.Name
FROM (SELECT f.ID, s.Name, f.Friend_ID, p.Salary AS first_Salary
      FROM Students s
       INNER JOIN Friends f ON s.ID = f.ID
       INNER JOIN Packages p ON f.ID = p.ID) sub
 INNER JOIN Packages p ON sub.Friend_ID = p.ID
WHERE sub.first_Salary < p.Salary
ORDER BY p.Salary

풀이

1. 첫번째로 전체테이블을 먼저 연결해 봅니다.

SELECT f.ID, s.Name, f.Friend_ID, p.Salary
FROM Students s
INNER JOIN Friends f ON s.ID = f.ID
INNER JOIN Packages p ON f.ID = p.ID;

모든테이블을 INNER JOIN으로 연결해 주고 ID와 NAME, FRIEND_ID. SALARY를 출력해보면
다음과 같이출력됩니다.

이때 저희는 학생과 친한친구 의 Salary를 비교해야 하기 때문에 (아~ 그럼 나중에WHERE 절로 비교연산자를 사용하겠구나~) 두개의 Salary가 필요합니다. 즉, 서브쿼리를 이용해야한다는것을 짐작해 볼 수 있습니다.

2. 서브쿼리 만들기

SELECT *
FROM (SELECT f.ID, s.Name, f.Friend_ID, p.Salary AS first_Salary
      FROM Students s
    INNER JOIN Friends f ON s.ID = f.ID
    INNER JOIN Packages p ON f.ID = p.ID) sub

위 처럼 서브쿼리를 만들고 전체를 출력해 보면 다음과 같이 출력이 됩니다.

3. 친구의 Salary 표현하기
   이제 서브쿼리를 넣었으니 이를 활용해서 친구의 salary를 테이블상에 나타내어줘야 WHERE절로 친구의 Salary가 더 높은 것으로 걸어줄 수 있습니다. 기존 테이블을 보면 학생의 아이디와 친구아이디 그리고 Salary 가 있는데 이때 학생에 대한 Salary만 나와있고 그학생에 가장친한친구의 Salary를 표현해주기 위해서는 가장친한친구의 id를 자기 id와 Salary 정보가 있는 Packages와 join을 해주게되면 "학생id, 친구id, 학생 salary, 친구id, 친구salary" 모양을 볼 수 있을것 같습니다.

SELECT *
FROM (SELECT f.ID, s.Name, f.Friend_ID, p.Salary AS first_Salary
      FROM Students s
    INNER JOIN Friends f ON s.ID = f.ID
    INNER JOIN Packages p ON f.ID = p.ID) sub
INNER JOIN Packages p ON sub.Friend_ID = p.ID

위와 같이 서브쿼리로 만들어준 테이블의 친구id와 id별로 salary를 표현해준 packages테이블의 id와 join을 하게되면 뒤에 친구 id에 따른 salary가 나오게됩니다.

파란색은 기존 학생의id, 빨간색은 친구id와 친구의 id에따른 Salary가 조합된 모습입니다.

순서가 바뀐이유는 친구id와 id정보를 담은 테이블끼리 조인했기때문에 1부터 나열된 모습입니다.

이제는 조건을 걸어주면 마무리가 됩니다.

4. 조건걸어주기

SELECT sub.Name
FROM (SELECT f.ID, s.Name, f.Friend_ID, p.Salary AS first_Salary
      FROM Students s
       INNER JOIN Friends f ON s.ID = f.ID
       INNER JOIN Packages p ON f.ID = p.ID) sub
INNER JOIN Packages p ON sub.Friend_ID = p.ID
WHERE sub.first_Salary < p.Salary
ORDER BY p.Salary