Placements - HackerRank

Placements 문제

출처 : HackerRank Placements

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

Q.

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

Sample Output

Samantha
Julia
Scarlet

---

질문

1) 자신보다 친구가 연봉이 더 많은 사람을 찾기
2) 출력은 사람의 name만
3) 정렬은 친구의 연봉 순서대로 오름차순

내 답안 - 1 📕

SELECT students_name
FROM (SELECT s.id AS students_id
           , s.name AS students_name
           , p.salary AS students_salary
           , f.friend_id AS friend_id
      FROM Students AS s
        INNER JOIN Packages AS p ON s.id = p.id
        INNER JOIN Friends AS f ON s.id = f.id) AS ijt
  INNER JOIN Packages AS p ON ijt.friend_id = p.id
  INNER JOIN Students AS s ON ijt.friend_id = s.id
WHERE students_salary < p.salary
ORDER BY p.salary ASC;

FROM 절에 서브쿼리를 써서 복잡하게 풀었다.
친구의 이름까지 출력해서 볼려고 안해도 될 짓을 했다.

내 답안 - 2 📕

SELECT s.name AS my_name
    --  , s.id AS my_id
    --  , p.salary AS my_salary
    --  , f.friend_id AS fr_id
    --  , p2.salary AS fr_salary
FROM Students AS s
    INNER JOIN Packages AS p ON s.id = p.id
    INNER JOIN Friends AS f ON s.id = f.id
    INNER JOIN Packages AS p2 ON f.friend_id = p2.id
WHERE p.salary < p2.salary
ORDER BY p2.salary ASC;

출력

Stuart
Priyanka
Paige
Jane
Julia
Belvet
Amina
Kristeen
Scarlet
Priya
Meera