www.hackerrank.com/challenges/placements/problem
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 |