[HackerRank] Placements Solution(풀이 2가지)

Placements 문제 바로가기 

Placements | HackerRank

 

 

문제

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).

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.

 

친구가 더 높은 salary를 받는 사람의 이름이 나오도록 쿼리 입력
결과값 정렬은 친구의 salary순으로 정렬 
같은 salary를 받는 학생은 없음

---

풀이

1. 테이블 3개를 조인하는 방법

SELECT s.name
FROM students s
           JOIN friends f ON s.id = f.id
           JOIN packages p ON s.id = p.id
           JOIN packages p2 ON f.friend_id = p2.id
WHERE p.salary < p2.salary
ORDER BY p2.salary;

 

2. 서브쿼리를 이용하는 방법

SELECT me.Name
FROM (SELECT s.ID, s.Name, f.Friend_ID, p.salary AS me_salary
             FROM students s
            JOIN friends f ON s.ID = f.ID
            JOIN packages p ON s.ID = p.ID) AS me
JOIN packages p2 ON me.Friend_ID = p2.ID
WHERE me.me_salary < p2.salary
ORDER BY p2.salary;

---

Output

Stuart
Priyanka
Paige
Jane
Julia
Belvet
Amina
Kristeen
Scarlet
Priya
Meera