Placements HackerRank Solution | SQL - KodeSrc

Placements HackerRank Solutions | Practice | SQL | Advanced Join

The Placements - Hackerrank solution, which is a component of the SQL practice module on Hackerrank's Advanced join section, will be covered in this post. Now let's get going!

Problem Statement:

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

Students Table Schema:

Friends Table Schema:

 

Packages and All Combined Table Schema:

Task:

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.

Understanding the schemas:

You are given 3 tables Friends , Students and Packages.
Students table contains the ID and Name of the student itself, where as the Friends Table contains the Mapping of Students with their Best Friend Id's.
The Third table is Package table where the Student ID is Mapped with their respective Salary

So if we relate the 3 tables we get that Students table Related to Friends table and Students table is related to Packages table.

And we need to find the students who got less salary then their best friend.

So lest see the solution for Placements Question:

select s.Name from Students s
join Friends f on s.ID=f.ID
join Packages student_salary on s.ID=student_salary.ID
join Packages friend_salary on f.Friend_Id=friend_salary.ID

where friend_salary.Salary>student_salary.Salary
order by friend_salary.Salary

Understanding the Solution:

We used joins in this query as you might have guessed that we need to get data from different tables so we need to join them or use where clause, but the use of where clause solution doesn't click in the first go, so we try to solve it using INNER JOIN. 

So we join the Students table and Friends Table on the basis of the student id,that means that the virtual table will have ID|NAME|FRIEND_ID approximatly. 

Now we join this virtual table with the Package table to get the Salary of the Student itself. SO the table would be now ID|NAME|FRIEND_ID|STUDENT_SALARY. Now we have to get the salary of the best friend so we again join this with the vitual table , so the table becomes ID|NAME|FRIEND_ID|STUDENT_SALARY|FRIEND_SALARY. 

Finally we need to output the Names of the students who got less salary then their friend so we compare the friend's salary with student's salary, and order them in ascending order of the friend's salary. 

Output:






Conclusion:

We have discussed and understood the Placements Hackerrank problem using joins ,for more posts like this do follow the site and subscribe through email to get instant updates for latest posts.