Placements HackerRank Solution | SQL | Codeityweb

Placements HackerRank Solutions | Practice | SQL | Advanced Join

In this post we are going to discuss the Placements - hackerrank solution that is a part of SQL practice module on Hackerrank in Advanced join section. So lets start !

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:

 

 

Related posts:

• Top Competitors - HackerRank Solution
• Revising the Select Query 1- Hackerrank solution 
• Revising the Select Query II - Hackerrank Solution
• Pairs - Hackerrank Solution 

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.

Thank You For Reading!