Placements: SQL Advance join (Hackerrank Problem)

-

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.

Solution:

select temp1.sn
from (select S.ID si,S.Name sn,P.Salary ps from Students S join Packages P on S.ID=P.ID) temp1 join (select FF.ID fi,FF.Friend_ID fd,PP.Salary pps from Friends FF join Packages PP on FF.Friend_ID=pp.ID) temp2 on temp1.si=temp2.fi and temp1.ps<temp2.pps
order by temp2.pps asc;

Explanation:

As the problem states that we have to select the name of the friend whose salary is lesser than what his/her friend is having. To do so, we have 3 tables called Students (ID,Name), Friends(ID,Friend_ID) and Packages(ID,Salary), we are going to join Students with Packages such that we will have a view tha what each student is getting based on their ID. after doing the first join, name it temp1 table we are going to perform another join operation on table Friends and Packages based on Friend_ID and ID which in turn will give us the table with id,Friend_Id and Salary based on Friend ID, name it as Temp2. finally we are going to perform join operation on table Temp1 and Temp2 based on ID of Temp1 and Temp2  and where Temp1.salary<Temp2.salary which will give us the list of the names of students having their salary lesser than their friends, finally order it based on the based salary in ascending order. and you're done. :)

Don't forget to check my other blogs based on sql, android development and  somewhat of security and recovery field.

Hope this will help you and if you're having any query regarding the SQL you can post id down below i will get back to as soon as possible. thank you. have a nice day... :) 

_______________________________The End__________________________________
Hoping this would be helpful to you, if not, post your problem in the comment section and i will try to get back to you as soon as possible.

tell me, how do you like this solution? and what is there that i can improve...

follow me on hackerrank: www.hackerrank.com/satyansh_sagar

Comments

Post a Comment

Popular posts from this blog

Contest Leaderboard: hackerrank problem (Level medium)

-
Problem Statement: You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too! The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id , name , and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id . Exclude all hackers with a total score of from your result. Tables: Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.  Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.   Solutions: select h.hacker_id,h.name,sum(sscore) from Hackers h inner join (select s.hacker_id,max(score) as sscore from Submissions s g
Read more

The Occupation: hackerrank problem

-
Problem Statement: Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation . The output column headers should be Doctor , Professor , Singer , and Actor , respectively. Note: Print NULL when there are no more names corresponding to an occupation. Table:   'OCCUPATIONS' table contain the column 'Name' and 'Occupation' Solution: select doc,prof,singer,act from ( select row_number() over(partition by Occupation order by Name) ron,Name as n,Occupation as o from OCCUPATIONS ) pro pivot (max(n)  for o in ('Doctor' as doc,'Actor' as act,'Professor' as prof,'Singer' as singer))  order by ron asc; Explanation: in the above query statement i am using row_number() function with the 'partition by' clause to append one column that contains row_number for each row in the subquery. in the subquery while assigning row_number() we
Read more