drop table friend Create table friend (pid int, fid int) insert into friend (pid , fid ) values ('1','2'); insert into friend (pid , fid ) values ('1','3'); insert into friend (pid , fid ) values ('2','1'); insert into friend (pid , fid ) values ('2','3'); insert into friend (pid , fid ) values ('3','5'); insert into friend (pid , fid ) values ('4','2'); insert into friend (pid , fid ) values ('4','3'); insert into friend (pid , fid ) values ('4','5'); drop table person create table person (PersonID int, Name varchar(50), Score int) insert into person(PersonID,Name ,Score) values('1','Alice','88') insert into person(PersonID,Name ,Score) values('2','Bob','11') insert into person(PersonID,Name ,Score) values('3','Devis','27') insert into person(PersonID,Name ,Score) values('4','Tara','45') insert into person(PersonID,Name ,Score) values('5','John','63') select * from person select * from friend

Great approach Ankit! Here is my solution before checking yours out. I avoided CTE's completed and achieved the answer mostly using joins. Here is my solution; select p.personid, p.name, count(f.friendid) as number_of_friends, sum(pp.score) as friends_total_score from persons p right join friends f on f.personid = p.personid left join persons pp on pp.personid = f.friendid group by p.personid, p.name having sum(pp.score) > 100

Great approach. I reached the solution by joining on the personid in the friends table. That led me to create 2 cte. Even though I reached the solution by that approach, your solution is more optimal

Hi Ankit sir, thank you for this amazing problem, your videos are the reason I am getting better everyday Here is my solution- with cte as (select f.*, p.Name, p.Score from [dbo].[Person$] as p inner join [dbo].[Friend$] as f on p.personID = f.FriendID ), cte2 as( select *, sum(Score) over (partition by PersonID order by (select null)) as total_marks from cte), cte3 as( select DISTINCT PersonID, total_marks, count(FriendID) as no_of_friend from cte2 group by PersonID, total_marks having total_marks>100) select c.PersonID, p.Name, c.no_of_friend, c.total_marks from cte3 as c inner join [dbo].[Person$] as p on c.PersonID = p.PersonID

Hi Ankit, I have a question - p.Name could have been added within the cte itself right? Why was the cte made and again joined with person table?

with cte as ( select a.*, b.friendid from person a inner join friend b on a.personid=b.personid ) select a.personid,a.name,count(a.friendid) no_of_friends,sum(b.score) total_score from cte a join person b on a.friendid=b.personid group by a.personid,a.name having sum(b.score)>100 order by a.personid , Thanks for the question with explanation

Hi @Ankit My solution :) with cte1 as ( select distinct f.PersonID, p.Name, sum(Score) over(Partition by f.PersonID order by f.PersonID) as total_marks from friend f inner join Person p on f.FriendID = p.PersonID ) ,cte2 as ( select distinct PersonID, count(Name) Over(Partition by PersonID Order by PersonID) as no_of_friends, total_marks from cte1 where total_marks > 100 ) select cte2.PersonID, p.Name, cte2.no_of_friends, cte2.total_marks from cte2 inner join Person p on cte2.PersonID = p.PersonID

Thanks for the video, you solved it so easily, WOW, I got so confused that I took almost an hour figuring out such easy question. Here's my solution though: comment => JOIN the two tables WITH CTE as (SELECT person.person_id as person_id, person.name as name_of_person, person.score as score, friends.friend_id as friend_id FROM person LEFT JOIN friends ON person.person_id = friends.person_id) comment => JOIN the CTE with a subquery to create a table with friend's score ,FT as (SELECT CTE.person_id as person_id, CTE.name_of_person as name_of_person, CTE.friend_id as friend_id, S.score as friend_score FROM CTE INNER JOIN (SELECT person_id, name, score FROM person) S ON CTE.friend_id = S.person_id) comment => obtain final results using group_by SELECT person_id, name_of_person, count(friend_id) as friend_count, SUM(friend_score) as total_friend_score FROM FT GROUP BY person_id, name_of_person HAVING total_friend_score > 100;

Hi Ankit, I have a query in this question. Why don't you included person name in the output from the select statement by doing p.name instead of creating CTE? wont we do it without creating CTE?

This question considers it is a one way friendship. To make it more interesting, also assume that it's a two way friendship. That way, 3 is friends with 1, 5, and 4. Query for that: (this can be optimized further, but this works too) with cte as ( select distinct id1, count(id2) over (partition by id1 order by id1) as no_of_friends, sum(p.score) over (partition by id1 order by id1) as friend_score from ( select PersonID as id1, FriendID as id2 from Friends UNION ALL select FriendID as id1, PersonID as id2 from Friends)t join Person p on t.id2 = p.PersonID) select a.id1 as PersonId, b.name as Name, no_of_friends, friend_score as sum_of_marks from cte a join Person b on a.id1 = b.PersonID where friend_score > 100

There can be some modification. If 3 is friend of 1 Then 1 should be friend of 3 Same goes with 2-3, 3-5, 4-2,4-3,4-5 So my query to get the output . . with table1 as ( select friend_id ,person_id from ( select person_id , friend_id , count(*) over( partition by x.sum_of) as sum_of from ( select person_id , friend_id , sum_of = person_id + friend_id from friend ) x ) y where y.sum_of = 1 ) , table2 as ( select * from friend union select * from table1 ), table3 as ( select T1.PersonID , T1.Name , T2.friend_id , T1.score from person T1 join table2 T2 on T1.PersonID = T2.person_id ) , table4 as ( select distinct T1.PersonID , T1.Name , T1.friend_id , T2.score from table3 T1 left join table3 T2 on T1.friend_id = T2.PersonID ) select PersonID , Name , number_of_friends,sum_score from ( select PersonID , Name , count(*) as number_of_friends , sum(score) as sum_score from table4 group by PersonID , Name ) x where x.sum_score > 100

create table and insert also please it will save time and focus on logic. keep going good collection of questions.

Very well explained...👍❤

This problem covers lot of area, I generally dont use having, I tried in my way. Thanks for the problem Ankit, with score_cte as ( select Person.PersonId,Person.Name,Friend.FriendId,Person. Score from Person join Friend on Person.PersonId = Friend.PersonId ), total_score_cte as ( select PersonId,score_cte.Name, SUM(score_cte.Score) over (partition by PersonId order by PersonId) as total_score , ROW_NUMBER() over (partition by PersonId order by PersonId) as rn , Count(FriendId) over (partition by PersonId order by PersonId) as total_friends from score_cte ) select PersonId,Name,total_friends,total_score from total_score_cte where total_score_cte.total_score > 100 and total_score_cte.rn = 1

beautifully explained, thank you very much :)

@ankitbansal - Please correct me if i am wrong, but as per the question - we need to find the who has friends with marks greater then 100. That means we need to display the sum of person marks, but not the friend marks. So, anyway i have included both marks in output in my query as below - with t as ( select f.PersonID, p.name, p.score as person_score, f.friendid, p2.name as friend_name, p2.score as friend_score from friend f left join person p on f.PersonID = p.PersonID left join person p2 on f.FriendID = p2.PersonID ) select personid, name, count(friendid) as number_of_friends, sum(person_score) as sum_of_marks, sum(friend_score) as sum_of_friend_marks from t group by 1, 2 having sum(friend_score) > 100

Sir , I have one doubt why you used "with" clause , for getting the person name why don't you insert p.name in the expression , Like Select p.name , f.personid , sum(p.score) as total_friend_score ..... Sir apne esa kyu nahi kiya ye smj me nahi aaya 2 baar join kyo kiyaa??? Please explain me

select a.personid,a.name,b.tot_score,b.no_of_friends from person a inner join (select f.Personid,sum(p.score) tot_score,count(f.friendID) no_of_friends from person p inner join friend f on p.Personid=f.friendID group by f.Personid having sum(p.score)>100)b on a.personid=b.Personid

actually in the end p.score should also have been there I think , because the initial question was to also find the score of the person whose friends' score is over 100

I WAS NOT ABLE TO SOLVE FIRST 5 QUESTIONS OF THIS PLAYLIST. BUT FINALLY I DID IT WITHOUT USING ANY HELP. I HAVE JUST STARTED LEARNING SQL. THANK YOU SIR FOR YOUR ALL EFFORTS. WITH CTE_1 (P_ID, P_NAME, NUMBER_OF_FRIENDS) AS ( select F.personid, P.name, SUM(CASE WHEN F.friendid P.PERSONID THEN 1 ELSE 0 END) AS NUMBER_OF_FRIENDS from friend F INNER JOIN person AS P ON F.personid = P.personid GROUP BY F.personid, P.name ), CTE_2 (P_ID1, TOTAL_SCORE_OF_FRIENDS) AS ( select F.personid, SUM(P.score) from friend AS F INNER JOIN person AS P ON F.friendid = P.personid GROUP BY F.personid ) SELECT P_ID, P_NAME, NUMBER_OF_FRIENDS, TOTAL_SCORE_OF_FRIENDS FROM CTE_1 INNER JOIN CTE_2 ON P_ID1 = P_ID WHERE TOTAL_SCORE_OF_FRIENDS > 100

Simple Mysql Sol : select p1.personid,p1.name,count(fid) as number_of_friends,sum(p2.score) as friends_sum from person p1 join friend f on p1.personid = f.pid join person p2 on p2.personid = f.fid group by 1,2 having sum(p2.score)>100

Thanks for the video.

This was a bit simple one just we have to join to different nested tables select A.PersonID, A.Name, A.no_of_frnd,B.Total_frnd_marks from ( select P.PersonID,P.Name,b.no_of_frnd from person as P left join ( select PersonID,count(PersonID)as no_of_frnd from friends group by PersonID)b on P.PersonID=b.PersonID )A inner join ( select PersonID, sum(score) as Total_frnd_marks from ( select f.PersonID,f.FriendID,P.score from person as P inner join friends as f on f.FriendID=P.PersonID)a group by PersonID )B on A.PersonID=B.PersonID where B.Total_frnd_marks>100

wonderful explanation!

select p.personid,p.name,t.score,friends from Person p inner join (select f.personid,sum(score) score,count(f.FriendID)friends from friend f left join Person p on p.PersonID=f.friendid group by f.personid having sum(score)>100)t on t.personid=p.personid

I am not able to understand why you are joining on f.friendid=p.personid, shouldn't it be f.personid=p.personid?

i written and worked... with ctte as (select personid,sum(score) as total,count(friendid) as no_of_frnds from (select f.personid as personid,p.name,f.friendid,p.score as score from person p inner join friend f on p.PersonID=f.friendid) as pf group by personid having total>100) select ctte.no_of_frnds,ctte.total,ctte.personid,person.name from ctte left join person on ctte.personid=person.PersonID ;

Hi Sir! we practise on oracle as well so please have the schema posted in other than the dataset.

I used below query, select p.personid,p.name,p.score,count(1) noof,sum(p1.score) dd from person p inner join friend f on p.personid=f.personid inner join person p1 on p1.personid=f.friendid group by p.personid,p.name,p.score having sum(p1.score)>100

Completed ❤

With frnd as (Select b.personid, b.friendid, a.name, a.score from #person a join #friend b on a.personid = b.friendid), score as (Select personid, count(1) as no_of_friends, sum(score) total_friend_score from frnd group by personid ) Select a.personid, b.name, a.no_of_friends, a.total_friend_score from score a join #person b on a.personid = b.personid and a.total_friend_score > 100

Could you please tell why did you join Person table on Friend table? Because I did the opposite and the total_friend_score are totally different wrong in my case.

my approach: with cte as( select p.*,f.FriendID,p2.Name as fname,p2.score as fscore from person p inner join friend f on f.personid = p.PersonID inner join person p2 on f.FriendID = p2.PersonID order by p.personid) select personid,name,count(*) as no_of_friends,sum(fscore) as fscores from cte group by personid,name having fscores>100

Nice video bro

Great explanation

select * from ( select personid,name,count(friendid)friend_count,sum(friend_score) as marks from (select a.personid,a.name,a.email,a.score as person_score,b.friendid ,c.score as friend_score from people a join friend b on a.personid=b.personid join people c on b.friendid=c.personid) group by 1,2) where marks>100;

with cte as ( SELECT A.PersonID, count(a.friendid) totalfrn, sum(B.score) as frndtotalscore FROM friend A inner join person B on A.FriendID = b.personid group by A.PersonID having frndtotalscore>100 ) select Z.personid,Z.totalfrn, Z.frndtotalscore , C.name from cte Z inner join person C on z.personid=c.personid

If anyone could let me know why here join is on friend id and person id?

Below is one more solution that i tried : with tab as (select b.id,a.name,a.score,count(a.name) over (partition by b.id) cnt_frnds,sum(a.score) over (partition by b.id) sum_score from person a,friend b where a.id=b.friend_id) select distinct tab.id,person.name,tab.cnt_frnds,tab.sum_score from tab,person where tab.id=person.id and tab.sum_score>100 order by tab.id

thanks

with x as (select a.*, b.score as friend_score from friend as a inner join person as b on a.friendid=b.personid), y as (select a.personid,a.name,x.friendid,x.friend_score from person as a left join x on a.personid=x.personid), z as (select y.*,sum(friend_score) over(partition by personid)as total_score ,count(friendid) over (partition by personid) as total_friends from y) select distinct personid,name,total_friends,total_score from z where total_score>100;

Nice video🙏🏻

I went with the below query not sure whether it's an optimized approach but it did give wat is expected WITH stage1 AS( SELECT f.personID,f.friendID,p.score FROM friend AS f LEFT JOIN person p ON f.friendId=p.personID ), stage2 AS ( SELECT personID,sum(score) AS total_friends_score,count(friendId) AS no_of_frds FROM stage1 GROUP BY personID) SELECT p.personID,p.name,s.total_friends_score,s.no_of_frds FROM person AS p INNER JOIN stage2 AS s ON p.personID=s.personID WHERE s.total_friends_score > 100;

We don't need CTE here. I was also doing same things earlier but later I did like this. select f.PersonID as personid,p.name,count(FriendID) as no_of_friends,sum(p.score) as total_friendscore from friend f join person p on f.FriendID = p.PersonID group by f.PersonID having sum(p.score) > 100;

with cte as ( select f.personid, p.name from friends as f left join person as p on f.personid=p.person id) select c.personid , c.name , count (personid) as number_of_friends, sum(marks) as total_marks_of_friends from cte as c left join person as p on c.friendid=p.personid group by c.name ,c.personid having total_marks_of_friends > 100

thank you for the questions! here is a solution with only joins select ttt.pid,name,no_of_friends,marks as total_friendscore from(select * from( select pid,sum(score) as marks,count(*) as no_of_friends from (select friend.pid,fid,name,score from friend join person on friend.fid=person.pid )ttt group by pid )ttt where marks>100)ttt join person on person.pid=ttt.pid;

My solution for this problem: with cte as( Select personID,SUM(score) sum_of_friends_marks from (Select f.*,p.score from Friend f inner join person p on f.friendID=p.personID) a group by personid), cte1 as( Select p.personid,p.name,f.number_of_friends from Person p inner join (Select personid, COUNT(friendID) number_of_friends from Friend group by personid) f on f.personid=p.personid) Select cte1.*,cte.sum_of_friends_marks from cte inner join cte1 on cte.personid=cte1.personid where sum_of_friends_marks>100

My Solution: with cte as (select a.PersonID, a.Name, c.Score from Person a inner join Friend b on a.PersonID=b.PersonID inner join Person c on b.FriendID = c.PersonID) select PersonID, Name,count(*), sum(Score) from cte group by PersonID, Name having sum(Score) >100

Solution in Oracle: with cte as ( select query1.*,query2.score as friend_score from ( (select p.*,f.friendid from Person p inner join friend f on (p.person_id=f.personid)) query1 inner join person query2 on (query2.person_id=query1.friendid))) select person_id,name,number_of_friends,total_sum from ( select person_id,name,count(*) over (partition by person_id) as number_of_friends, sum(friend_score) over(partition by person_id) as total_sum from cte) where total_sum>100 group by person_id,name,number_of_friends,total_sum; DDL: create table person ( person_id integer, name varchar(20), email varchar(100), score integer ); create table friend ( personId integer, friendId integer);

shouldn't both PersonID and FriendID be friends to each other ? If we consider only one way friednship ..FriendID is only friend to PersonID and not vice-versa then below query gives expected result with temp as ( select tf.PersonID pid ,p1.name pname, count(tf.FriendID) no_of_friends, sum(p2.score) fmarks from friend tf inner join person p1 on tf.PersonID = p1.PersonID inner join person p2 on tf.FriendID = p2.PersonID group by tf.PersonID,p1.name ) select pid,pname,no_of_friends,fmarks from temp where fmarks > 100

with cte as( select p.*,f.fid from person p left join friend f on p.personID=f.pid) select cte.personID,cte.name,count(1) as no_of_friends,sum(person.score) as sum_score from cte left join person on cte.fid=person.personID group by cte.personID,cte.name having sum(person.score)>100 ;

with cte as (select f.personid, count(1) as total_friends, sum(score) as score from friend f join person p on f.friendid=p.personid group by f.personid having score>100) select c.*,p.name from cte c join person p on c.personid=p.personid

with cte as ( select f.personId as personId,f.friendID as friendID, p.score, sum(p.score) over (partition by f.personId) as marks_sum from friend f left join person p on f.friendId = p.PersonId ) select personId,count(friendID) as no_of_friends,marks_sum from cte where marks_sum > 100 group by personId,marks_sum ;

Where to find the script for this

Why can't we simply join these 2 tables and use GROUPBY, my solution is working fine: SELECT p.personID, Name, COUNT(1) as No_of_Friends, SUM(Score) Sum_of_marks FROM Friends f JOIN person p ON p.PersonID = f.PersonID GROUP BY p.personID, Name HAVING SUM(Score) >100

select p.personid,p.name --,f.friendid , count(1) as no_of_friends , sum(p1.score) as total_friend_score from friend f inner join person p on p.personid=f.personid inner join person p1 on f.friendid=p1.personid group by p.personid,p.name having sum(p1.score)>100

Can we get the data, so that we can practice?

used partition , with cte as ( select N.Name , F.PersonID,F.FriendID , P.Name as FriendsName,P.Score, Sum(P.Score) over (partition by N.Name) as TotalMark, count(*) over (partition by N.Name) as NoFriends from [dbo].[Friend] F left join [dbo].[Person] P on F.FriendID = P.PersonID left join [dbo].[Person] N on F.PersonID = N.PersonID ) select distinct Name , TotalMark ,NoFriends from cte where TotalMark > 100 order by Name

My solution without looking into the video (MySQL): select p2.personid,p2.name,count(p.personid) as no_of_friends,sum(p.score) as total_friends_score from person p join ( select p1.*,f.friendid from person p1 join friend f on f.personid = p1.personid) p2 on p2.friendid = p.personid group by p2.personid having sum(p.score) >= 100;

select f.personid,pp.Name,sum(p.score)score from friend f inner join Person p on f.friendid=p.PersonID left join Person pp on f.personid=pp.PersonID group by f.personid,pp.Name having sum(p.score)>100

CREATE TABLE person( PersonID int, Name VARCHAR(1000), Email VARCHAR(500), Score int );

With cTE1 as( SELECT f.personid,p.NAME,p.EMAILID,sum(p.SCORE) over(partition by f.PERSONID order by p.PERSONID) as finalscore,count(f.PERSONID) over(partition by f.PERSONID) as totalfriend from Person p inner join Friend f on p.PERSONID = f.friendid ) SELECT * from cTE1 WHERE finalscore >100 I have written this solution for this query

select t2.id ,sum(t1.marks) as sm from friend as inner join person as t2 on t1.friend_id=t2.personID group by t2.id having sm>100;

with cte1 as (select f.personid,count(*),sum(score) ascore from friend f join person p on f.friendid=p.personid group by f.personid having ascore>100) select cte1.*,name from cte1 join person using (personid) (easy way!)

select s.name ,A.* from person s inner join (select f.personid,sum(p.score) ,count(1) from friend f inner join person p on p.personid=f.friendid group by f.personid having sum(p.score)>100) A on s.personid= A.personid;

with cte1 as( select f.PersonID,p.Name,f.friendID from person as p,friend as f where p.PersonID=f.PersonID ),cte2 as ( select cte1.PersonID,cte1.Name,p.Score from cte1 as cte1,person as p where cte1.friendID=p.PersonID ) select cte2.PersonID,cte2.Name,count(cte2.name) as no_of_friends,sum(cte2.score) as total_friend_score from cte2 as cte2 group by cte2.PersonID,cte2.Name having sum(cte2.score)>100

11/142

MYSQL with base as (select p.person_id,p.name,p.score,p1.name as friend_name,p1.score as friend_score from person p join friend f on p.person_id = f.person_id join person p1 on f.friend_id = p1.person_id order by person_id) select person_id,name,count(1) as no_of_friends, sum(friend_score) as total_score from base group by 1,2 having sum(friend_score) > 100;

can anyone find the solution for this. Get the list of managers who manage more than two departments? Display manager first name, manager salary and the number of departments managed tablename : Employee e_id f_name l_name salary dept_id dept_start dept_end_dt tablename : department d_id d_name d_city d_mgr_id

with cte1 as ( select p.personid, p.name as person_name, p.score as person_score, f.friendid from person p left join friend f on p.personid = f.personid), cte2 as ( select a.personid, a.person_name, --a.person_score, a.friendid, b.name as friend_name, b.score as friend_score from cte1 a left join person b on a.friendid = b.personid ) select personid,person_name as name, count(friendid) no_of_friends, sum(friend_score) as total_friendscore from cte2 group by personid,person_name having total_friendscore > 100

My version with friends as ( select p.PersonID, Name, FriendID from person p inner join friend f on p.PersonID = f.PersonID ), score as ( select f.*, p.Score from friends f join person p on f.FriendID = p.PersonID ) select PersonID, Name, count(total_friends) as total_friends, total_score from ( select PersonID, Name, count(FriendID) over(partition by PersonID) as total_friends, sum(score)over(partition by PersonID) as total_score from score )as x where x.total_score > 100 group by PersonID, Name, total_score order by PersonID

with cte as ( select f.PersonID,p.Name,count(f.FriendID) no_of_friends ,sum(cast(p1.Score as int)) total_friendScore from Person p join Friend f on p.PersonID = f.PersonID join Person p1 on p1.PersonID = f.FriendID group by f.PersonID,p.Name ) select * from cte where total_friendScore >= 100

with temp as( select f.personid,pp.name, sum(p.score) friends_total_score from friend f inner join person p on f.friendid = p.personid inner join person pp on f.personid = pp.personid group by f.personid , pp.name having friends_total_score > 100), temp2 as (select personid, count(friendid) total_friends from friend group by personid) select temp.* , temp2.total_friends from temp inner join temp2 on temp.personid = temp2.personid;

with cte1 as ( select p.personid, p.name as person_name, f.friendid--, p.score as personscore from person p inner join friend f on p.personid = f.personid) select frnd.personid, frnd.person_name, count(*) as no_of_frnds--,frnd.friendid ,sum(pr.score) as friend_Score from cte1 frnd inner join person pr on pr.personid = frnd.friendid group by frnd.personid, frnd.person_name having sum(pr.score)>100 ;

;with cte_friend_Score as ( select total_score = sum(score) ,f.personid ,noOfFriend = count(f.personid) from friend f inner join person p on p.PersonID = f.FRIENDID group by f.PERSONID having sum(score) >100 )

with person_friend as( select p.personid,p.name,p.email,f.friendid FROM Person p LEFT JOIN friend f ON p.Personid = f.Personid Where f.Personid is not null ) select p.personid,p.name,count(1) as no_of_friends ,sum(f.score) as total_friendscrore from person_friend p JOIN Person f ON p.friendid = f.Personid group by 1,2 having sum(f.score) > 100 ;

This is my solutionn using two joins without using CTE select t.PersonID,jt1.Name,jt1.Score as PersonMark, count(t.FriendID) as FriendCnt, sum(jt2.Score) as FriendsTotalMarks from Friend as t left join Person_marks as jt1 on jt1.PersonID=t.PersonID left join Person_marks as jt2 on jt2.PersonID=t.FriendID group by t.PersonID,jt1.Name,jt1.Score having sum(jt2.Score)>100

select p.personid,p.name, p.email, count(p2.personid) as num_friends, sum(p2.score) as friend_score from person p left join friend f on p.personid=f.personid left join person p2 on f.friendid = p2.personid group by p.personid,p.name,p.email having sum(p2.score)>100

select p2.PersonID, p2.name, count(p2.name) as no_of_friends, sum (p1.score) as marks from friend F left join Persons p1 on p1.personId =F.FriendID left join Persons p2 on p2.personId =F.PersonID group by p2.PersonID,p2.name having sum (p1.Score) >100

/* Write an sql query to find person id ,name ,number of friends,sum of marks who have friends with total score greater than 100*/ with friends_score as ( with bte as ( with cte as ( select f.person_Id ,f.friend_ID,p.score as friend_score from persons p inner join friend f on p.person_Id=f.friend_Id) select p.name,p.person_ID,c.friend_Id,friend_score from persons p inner join cte c on c.person_ID=p.person_Id order by p.person_Id asc ) select name,person_id,count(friend_Id) as total_friends ,sum(friend_score) as friends_score from bte group by name,person_id) select * from friends_score where friends_score >100 /* IN first cte just find and join the friends id with score , 2.In second cte for name of the friend join it with friend , 3.in third cte call it by where friends_score>100*/ I know its a little bit complex approach but can be done with nested cte also

select p.PersonID,p.Name,count(*) as cnt, sum(p1.score) as totalscore from friends f left join Persons p on f.PersonID=p.PersonID left join Persons p1 on f.FriendID=p1.PersonID group by p.PersonID,p.Name having sum(p1.score)>100

select pid,g.name as p_mname ,sum(p.score) as f_score,count(g.score) as No_friend from friend f join person p on f.fid=p.PersonID join person g on pid=g.PersonID group by pid,g.name having sum(p.score)>=100

Is this the answer for two way friendship? PersonID Name num_friends Scores 2 Bob 3 160 3 Davis 4 207 4 Tara 3 101

With CTE as ( select f.pid,f.fid, p.score as friends_score , SUM(p.score) OVER(partition by f.pid) as friends_total_score, COUNT(f.fid) OVER(partition by f.pid) as no_of_friend from person p INNER JOIN friend as f ON p.personID=f.fid) Select distinct pid,person.name, no_of_friend, friends_total_score, from CTE INNER JOIN person ON CTE.pid=Person.personID where friends_total_score>100

WITH CTE AS( SELECT a.personID, a.name, b.friendID, a.score, c.score as firend_score from person a INNER JOIN friend B ON a.personID = b.personID INNER JOIN person C ON B.friendID = c.personid ) SELECT PERSONID, NAME, COUNT(distinct friendID) as no_of_frnds, SUM(firend_score) as total_friend_score from cte GROUP BY 1,2 HAVING SUM(firend_score) >= 100

WITH t1 as (SELECT f.personid,COUNT(f.personid) as no_f,SUM(p.score) as total FROM friend f JOIN person p ON f.friendid=p.personid GROUP BY f.personid HAVING total>100) SELECT * FROM person p JOIN t1 ON t1.personid=p.personid

WITH HGH AS (SELECT f.PersonID,SUM(p.Score) AS total_score_friends ,COUNT(1) as no_of_friends FROM person p INNER JOIN friend f on p.PersonID=f.FriendID GROUP BY f.PersonID) SELECT p.PersonID,p.Name,h.no_of_friends,h.total_score_friends FROM HGH h INNER JOIN person p on h.PersonID=p.PersonID WHERE total_score_friends>100; I also used similar approach

with friend_score as (select f.PersonID as PersonID, sum(p.Score) as Score from friend f join Person p on p.PersonID = f.FriendID group by f.PersonID), original as (select f.PersonID as PersonID,Name, count(f.FriendID) as no_of_friends from friend f join Person p on f.PersonId = p.PersonID join friend_score fc on f.PersonId = fc.PersonId group by f.PersonID, Name ) select o.*, fc.Score as total_friend_score from original o join friend_score fc on o.PersonID = fc.PersonID where fc.Score > 100

Select p.PersonID as Person_Id, p.Name as Person_name, count(p1.Name) as Person_friend_count, Sum(p1.score) as Score_of_friend from Person$ p inner join Friend$ f on p.PersonID=f.PersonID left join Person$ p1 on p1.PersonID=f.FriendID Group by p.PersonID,p.Name Having Sum(p1.score) > 100

Please review this approach SELECT PERSON_ID, NAME,COUNT(FRIEND_ID) TOTAL_FRIENDS,SUM(F_SCORE) TOTAL_FRIENDS_SCORE FROM ( SELECT P.PERSON_ID PERSON_ID,P.NAME name, F.FRIEND_ID FRIEND_ID,P2.SCORE F_SCORE FROM PERSON P JOIN FRIENDS F ON P.PERSON_ID = F.PERSON_ID JOIN PERSON P2 ON F.FRIEND_ID = P2.PERSON_ID ORDER BY P.PERSON_ID,F.FRIEND_ID ) GROUP BY PERSON_ID,NAME HAVING SUM(F_SCORE)>100

athlete totalmedals athlete1 8 athlete2 7 athlete3 7 athlete4 5 output: athlete totalmedals medal_diff athlete1 8 0 athlete2 7 1 athlete3 7 1 athlete4 5 3

with cte as ( select [Person$].personid as person_id , [Person$].name as person_name , [Friend$].FriendID as friend_id from [Person$] left join [Friend$] on [Person$].personID = [Friend$].personID ) , cte2 as (select cte.person_id, cte.person_name, cte.friend_id , [Person$].name as friend_name, [Person$].score as friend_score from cte left join [Person$] on cte.friend_id = [Person$].personID) select person_id, person_name, count(friend_id) as t_friends , sum(friend_score) total_marks from cte2 group by person_name, person_id having sum(friend_score)>100

CREATE temp TABLE PersonFriends ( PersonID INT, FriendID INT, PRIMARY KEY (PersonID, FriendID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID), FOREIGN KEY (FriendID) REFERENCES Persons(PersonID) ); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (1, 2); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (1, 3); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (2, 1); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (2, 3); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (3, 5); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (4, 2); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (4, 3); INSERT INTO PersonFriends (PersonID, FriendID) VALUES (4, 5);

with cte as ( select FriendID , score from testdb.person join testdb.friend on testdb.person.PersonID=testdb.friend.FriendID), cte_2 as (select distinct PersonId, F.FriendID, score from testdb.friend F join cte C on F.FriendID = C.FriendID order by PersonID) select testdb.person.PersonId, name,count(FriendID) as no_of_friends, sum(cte_2.score) as total_friendscore from cte_2 join testdb.person on cte_2.PersonID=testdb.person.PersonID group by PersonId, name having sum(cte_2.score) > 100 # Try running each CTE's to understand how it works

select f.PersonID, p1.Name, count(p2.PersonID) as number_of_friends, sum(p2.Score) as sum_of_marks from friend f inner join person p1 on f.PersonID = p1.PersonID inner join person p2 on f.friendID = p2.PersonID group by f.PersonID, p1.Name having sum(p2.Score) > 100;

SELECT f.personID, p1.name, COUNT(f.friendID) AS total_friends, SUM(p.score) AS total_score FROM friend AS f INNER JOIN person AS p ON f.friendID = p.personID INNER JOIN person AS p1 ON p1.personID = f.personID GROUP BY f.personID, p1.name HAVING SUM(p.score) > 100;