HackerRank: SQL Advanced Join PLACEMENTS | inner join Students, Friends, Packages in MySQL Query

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

HackerRank.com

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:

HackerRank.com

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

Solution with INNER JOIN (MySQL Query)

SELECT a.name
FROM Students a
INNER JOIN Friends b ON a.ID = b.ID
INNER JOIN Packages c ON a.ID = c.ID
INNER JOIN Packages c2 ON b.Friend_ID=c2.ID
WHERE c2.Salary > c.Salary
ORDER BY c2.Salary;

Expected Output

Stuart 
Priyanka 
Paige 
Jane 
Julia 
Belvet 
Amina 
Kristeen 
Scarlet 
Priya 
Meera

Note:

1. SELECT a.name: This query selects the "name" column from the "Students" table and gives it an alias "a" Aliases are used to refer to tables in a shorter way.
2. FROM Students a: This is the FROM clause that references the "Students" table and gives it an alias "a" which will be used to refer to this table in subsequent parts of the query.
3. INNER JOIN Friends b ON a.ID = b.ID: This is the first INNER JOIN. It joins the "Students" table with the "Friends" table using the matching "ID" column in both tables. The "Students" table is represented by the alias "a" and the "Friends" table is represented by the alias "b"
4. INNER JOIN Packages c ON a.ID = c.ID: This is the second INNER JOIN. It joins the "Students" table with the "Packages" table using the matching "ID" column in both tables. The "Packages" table is represented by the alias "c"
5. INNER JOIN Packages c2 ON b.Friend_ID = c2.ID: This is the third INNER JOIN. It joins the "Friends" table (represented by alias "b") with the "Packages" table (represented by alias "c2") using the "Friend_ID" column from the "Friends" table and the "ID" column from the "Packages" table.
6. WHERE c2.Salary > c.Salary: This is the WHERE clause that filters rows that meet the condition where the salary (Salary) of a friend (in table "c2") is greater than the salary of a student (in table "c" alias "a"). It only retains rows that satisfy this condition.
7. ORDER BY c2.Salary: This sorts the results by the "Salary" column from the "c2" table (i.e., the friend's salary) in ascending order.

So, the result of this query is a list of student names who have friends with higher salaries than themselves, sorted by their friends’ salaries in ascending order.