Hello, and welcome back, everyone. In this post, I’ll include the HackerRank Solution for the Placements in SQL. I’m going to use MySQL to solve this problem.
Problem
You are given three tables: Students, Friends, and Packages. Students contain two columns: ID and Name. Friends contain two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contain 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 the same salary offer.
Sample Input
Friends
| ID | Friend_Id |
|---|---|
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 1 |
Students
| ID | Name |
|---|---|
| 1 | Ashley |
| 2 | Samantha |
| 3 | Julia |
| 4 | Scarlet |
Packages
| ID | Name |
|---|---|
| 1 | 15.20 |
| 2 | 10.06 |
| 3 | 11.55 |
| 4 | 12.12 |
Sample Output
Samantha
Julia
Scarlet
Solution
select name from (select * from students as s natural join packages as p) temp1
inner join
(select f.id as id,friend_id,salary from friends as f inner join packages as p
on p.id=f.friend_id) temp2
on temp1.id=temp2.id
where temp1.salary<temp2.salary
order by temp2.salary;
Thanks for reading.
ProgrammingTech 