Extract the information

1. Output only the student name
2. Their best friend has Salary > them
3. ORDER Salary of Best Friends

So we need to compare the the salary of Friends and Best Friends. Questions: 1. How to write a query to compare Salary? Solution: using self join on table that has Salary which is Packages 2. We need to compare Salary based on ID and Friend_ID, how to do this? Solution: we want to compare Salary therefore Salary is the main query.

Steps 1. Let's do a SELF JOIN to get Salary of students and best friends

SELECT *
   FROM friends f
        ON s.id = f.id
INNER JOIN packages p1  --we determined p1 as students
        ON p1.id = f.id
INNER JOIN packages p2  --we determined p2 as best friend. You can see that we joined table Packages with Friend_id
        ON p2.id = f.friend_id

2. Add WHERE clause to compare the salary

       WHERE p2.salary > p1.salary
  ORDER BY p2.salary

3. Output required only the name so we need to use the Students table as main table and JOIN ther

    SELECT s.name
      FROM students s
INNER JOIN step1

4. Apply ORDER BY as required.

Below is the completed query.

SELECT s.name
FROM students s
INNER JOIN friends f ON s.id = f.id
INNER JOIN packages p1 ON s.id = p1.id
INNER JOIN packages p2 ON f.friend_id = p2.id
WHERE p2.salary > p1.salary
ORDER BY p2.salary;

with CTE as (select Stds.ID,Stds.Name,Frnd.Friend_ID as Best_Friend_ID, Pk.Salary as Student_Salary, BFPk.Salary as Bestfriend_Salary from students Stds inner join Friends Frnd on Stds.ID = Frnd.ID inner join Packages PK on Pk.ID = Stds.ID inner join Packages BFPk on BFPk.ID = Frnd.Friend_ID), CTE_2 as ( select case When Bestfriend_Salary > Student_Salary Then Name end as bfmorethanstudent,Bestfriend_Salary from CTE) select bfmorethanstudent from CTE_2 where bfmorethanstudent is not null order by Bestfriend_Salary

SELECT std.Name as Friend

FROM Students as std INNER JONI Friends as friend ON std.ID = friend.ID INNER JOIN Packages ON std.ID = Packages.ID INNER JOIN Packages as pck ON friend.Friend_ID = pck.ID WHERE pck.Salary > Packages.Salary ORDER BY pck.Salary ASC

Query:

WITH best_friend_salary as ( SELECT Students.id, name, salary as Person_Salary, Friend_id, (SELECT name FROM Students where id = friend_id) as Friend, (SELECT salary FROM Packages where id = friend_id) as Friend_Salary FROM Students JOIN Friends ON Students.id = friends.id JOIN Packages ON Students.id = Packages.id )

SELECT name FROM best_friend_salary WHERE Person_Salary < Friend_Salary ORDER BY Friend_Salary;

I have used CTE. A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.