Hackerrank – SQL – Advanced Joins – Placement – Solution

Publish Date - January 19th, 2023

|

Last Modified - March 2nd, 2023

A great question with the need to do multiple joins or pivot the data. Don’t overthink this one as it can get messy if you decide to try to do PIVOT or GROUP(CONCATENATE). What I learned from this problem is, you can Google or ChatGPT all the fanciest SQL functions in the world, sometimes it’s better to do things the simple way (Occam’s razor).

Overall, this problem was easier than Triangles 1 and Triangles 2 – but not as hard as Occupations. For Occupations, you need to have the mental capacity to understand how you’re manipulating the tables. Triangles 1 and 2 are just a looping through the table with some re-organization.

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

Sample Input

Sample Output

Samantha
Julia
Scarlet

Explanation

See the following table:

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

The Solution

Firstly, DO NOT let the table below confuse you 😠. I spent probably 20 minutes trying to recreate this table and then figured out that you don’t even need to do it!

As you can see, you would need to PIVOT, CASE or GROUP all three tables after a JOIN and then use some sort of ROW_COUNT across the top. Not really that easy in my honest opinion.

My solution:

/*
names WHERE friend_id > name
names ordered by friends.friend_id 


*/

WITH cte1 AS (
SELECT t1.id, t2.id AS t2id, t3.id AS t3id, t1.name, t2.friend_id, t3.salary AS normal_salary, ft3.salary AS friend_salary FROM students AS t1
JOIN friends AS t2 ON t1.id = t2.id 
JOIN packages AS t3 ON t1.id = t3.id
JOIN packages AS ft3 ON t2.friend_id = ft3.id
)


SELECT name FROM cte1
WHERE friend_salary > normal_salary
ORDER BY friend_salary

With the final output being:

Stuart
Priyanka
Paige
Jane
Julia
Belvet
Amina
Kristeen
Scarlet
Priya
Meera

Note the comparison between the salaries (second column is the original salary, 3rd column is their friend’s respective salary):

Stuart 13.15 14.1
Priyanka 11.1 15.5
Paige 12.1 15.6
Jane 13.1 16.7
Julia 15.6 17.1
Belvet 14.1 18.8
Amina 22.16 31.1
Kristeen 18.8 33.33
Scarlet 15.1 39
Priya 39 46
Meera 46 47

Now, my breakdown of my solution:

/*
names WHERE friend_id > name
names ordered by friends.friend_id 


*/

Just pseudo MySQL code to pull out the appropriate clauses and strategies. I know I have to do three things at the beginning:

1. JOIN all of the tables together to one table (there’s three tables)
2. Do a self join to compare one column vs. the same column but using another factor (in this case it’s friend_id + salary vs. id + salary).
3. Order by friend_id’s salary.

I also use t1, t2, t3 as table 1, table 2 and table 3.

WITH cte1 AS (
SELECT t1.id, t2.id AS t2id, t3.id AS t3id, t1.name, t2.friend_id, t3.salary AS normal_salary, ft3.salary AS friend_salary FROM students AS t1
JOIN friends AS t2 ON t1.id = t2.id 
JOIN packages AS t3 ON t1.id = t3.id
JOIN packages AS ft3 ON t2.friend_id = ft3.id
)

Next, I need to actually do the JOINs and I decide to do it in a common table expression (CTE) so it’s reusable (just a preference, you can just write the JOINs, SELECT name and order by after). Breaking down the CTE, I have 3 major portions:

WITH cte1 AS (
SELECT t1.id, t2.id AS t2id, t3.id AS t3id, t1.name, t2.friend_id, t3.salary AS normal_salary, ft3.salary AS friend_salary FROM students AS t1

This is where I make all of the columns accessible for follow on queries outside of the CTE. You don’t need to add all of the t1, t2, t3 ids but you will need to have the rest of the columns available. Note my aliases for “normal salary” and “friend salary” they will come in handy after you’re done the CTE.

JOIN friends AS t2 ON t1.id = t2.id 
JOIN packages AS t3 ON t1.id = t3.id

Next, two quick JOINs for these tables onto the master table (students AS t1), and next the most important part of the query!

JOIN packages AS ft3 ON t2.friend_id = ft3.id

This is where I self join friend_id back onto the master table where friend_id = id, so that we can easily compare salaries now. Without having this your queries will not work effectively.

Lastly, outside the CTE I do a quick SELECT statement for name, with the WHERE clause for the normal salary vs. friend’s salary, ordering by friend’s salary.

SELECT name FROM cte1
WHERE friend_salary > normal_salary
ORDER BY friend_salary

And we are finished!

The Conclusion

While not as tough as React, Python or even core Web development (Javascript) – I love doing these problems and writing about them. They keep my SQL skills sharp and also allow me to practice blogging, content creation and help folks out that may be stuck.

Feel free to leave a comment or let me know if there’s any other websites that have these types of problems. Note: I tried to use ChatGPT to solve this and it was unable. However, you can use ChatGPT to figure out how to do technical things like sub queries, joins and CTEs. Ultimately, it’s a great helper!