SQL & DB/HackerRank SQL Problem

[HackerRank SQL] Placements

YSY^ 2021. 3. 1. 21:22

www.hackerrank.com/challenges/placements/problem

 

Placements | HackerRank

Write a query to output the names of those students whose best friends got offered a higher salary than them.

www.hackerrank.com

 

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.

Sample Input

 

Sample Output

Samantha Julia Scarlet


Explanation

See the following table:

Now,

  • Samantha's best friend got offered a higher salary than her at 11.55
  • Julia's best friend got offered a higher salary than her at 12.12
  • Scarlet's best friend got offered a higher salary than her at 15.2
  • Ashley's best friend did NOT get offered a higher salary than her

The name output, when ordered by the salary offered to their friends, will be:

  • Samantha
  • Julia
  • Scarlet

Answer

select s.name
from students as s
    left join 
    packages as my_p
    on s.id = my_p.id
    left join
    friends as f
    on s.id = f.id
    left join
    packages as fr_p
    on f.friend_id = fr_p.id
where my_p.salary < fr_p.salary # students whose best friends got offered a higher salary than them
order by fr_p.salary

 

Result

 

728x90
반응형

'SQL & DB > HackerRank SQL Problem' 카테고리의 다른 글

[HackerRank SQL] Interviews  (0) 2021.03.01
[HackerRank SQL] Symmetric Pairs  (0) 2021.03.01
[HackerRank SQL] SQL Project Planning  (0) 2021.03.01
[HackerRank SQL] Contest Leaderboard  (0) 2021.03.01
[HackerRank SQL] Challenges  (0) 2021.03.01