We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Advanced Join
- Placements
- Discussions
Placements
Placements
Sort by
recency
|
1880 Discussions
|
Please Login in order to post a comment
WITH STD AS (SELECT ST.NAME AS STUDENT_NAME, FR.FRIEND_ID AS FRIEND_ID, PA.SALARY AS STUDENT_SALARY FROM STUDENTS ST JOIN FRIENDS FR ON ST.ID = FR.ID JOIN PACKAGES PA ON FR.ID = PA.ID),
FRI AS (SELECT FRR.FRIEND_ID AS FRIEND_ID, PAA.SALARY AS FRIEND_SALARY FROM FRIENDS FRR JOIN PACKAGES PAA ON FRR.FRIEND_ID = PAA.ID)
SELECT S.STUDENT_NAME FROM STD S JOIN FRI F ON S.FRIEND_ID = F.FRIEND_ID WHERE F.FRIEND_SALARY > S.STUDENT_SALARY ORDER BY F.FRIEND_SALARY
with first as ( select s.Name,p.Salary,f.ID,f.Friend_ID from Students s Join Friends f on s.ID=f.ID join Packages p on p.ID=f.ID ), second as (
select p1.Salary as Friends_salary,f1.id from Friends f1 join Packages p1 on f1.Friend_ID=p1.ID)
select first.name from first join second On first.ID=second.id where first.salary
Here's a simple solution in MySQL: Step 1: Calculate the salary of each student Step 2: Calulate the salary of each friend Step 3: Select the students where their salary is less than that of their friend
with student_salary as ( select s.id as id, s.name as name, sum(p.salary) as salary FROM students s inner join packages p on s.id = p.id group by s.id, s.name ), bestie_salary as ( select f.id as id, f.friend_id as bst_id, sum(p.salary) as salary from friends f inner join packages p on f.friend_id = p.id group by f.id, f.friend_id ), cte as ( select s.id as id, s.name as name, s.salary as student_salary, b.bst_id as friend_id, b.salary as friend_salary from student_salary s inner join bestie_salary b on s.id = b.id ) select name from cte where friend_salary > student_salary order by friend_salary asc
select student_name from (select s.id student_id, s.name student_name, p.salary student_salary,
f.friend_id friend_id, f.friend_name friend_name, f.friend_salary friend_salary from students s join packages p on p.id = s.id join (select ff.id, ff.friend_id, i.name as friend_name, pp.salary as friend_salary from friends ff join packages pp on pp.id = ff.friend_id join students i on i.id = ff.friend_id ) as f on f.id = s.id ) as mains where student_salary < friend_salary order by friend_salary