In this video we will discuss a scenario based SQL question. The solution will required join, group by and having clauses. Download data from below link: drive.google.c...
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
Thank you for amazing problem. Here is my solution select p.personID, p.Name,count(1) AS frnd_cnt,sum(q.score) AS frnd_score from person p Inner join friend f ON p.personID=f.pid INNER JOIN person q ON f.fid=q.personID Group BY p.personID,p.Name Having sum(q.score)>=100
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
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
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?
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
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
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
A slight change you need to do in your query. Instead of UNION ALL use UNION. When using UNION ALL, for person_id 2 it is giving 4 friends which is wrong. person_id 2 have 3 friends.
The below query will work in both cases 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 ;
two way friendship I have solved as below query with union_data as ( select personid as pid,friendid as frid from friend union all select friendid as pid,personid as frid from friend ),distinct_data as ( select distinct pid,frid from union_data ), friend_score as ( select pid as personid,count(frid) as no_of_friends,sum(score) as friend_score from ( select d.pid,p.name,d.frid,p.score from distinct_data d join person p on p.personid = d.frid ) group by pid having sum(score) > 100 order by pid ) select p.personid,p.name,fc.no_of_friends,p.score,fc.friend_score as friend_total_marks from person p join friend_score fc on p.personid = fc.personid
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
@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
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
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
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
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
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
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
Good question. If you see I have joined f.friendid to p.personid . So p.name will be friend name not the person name. I would suggest import the data and try yourself to make it crystal clear.
@@ankitbansal6 okay sir then why score not falls in the friend name as you used p.score at (3:10) in the expression like you said p.name will be friend name not the person name, because name and score both falls in the person table and you said p.name will be friend name not the person name. Little bit confused sir!!
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;
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 ;
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
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
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
@@ankitbansal6 i think u have missed part where 2 also has 4 as friend ..it's mentioend other way around in the table.if i am not missing something! Thanks for great content
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.
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
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;
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
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
Select Pid, P.name, Friends, F_score from person p join (Select Pid, f_score ,count(distinct fid) as friends from ( Select f.*, sum(p.score) over (partition by pid) as F_score from friend f left join person p on p.personid = f.fid) a where f_score >= 100 group by 1,2) h on H.pid =p.personid
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;
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
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;
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, 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
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 ;
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 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
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;
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 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
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
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;
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
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 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
select p.PersonID, p.Name, count(*) as no_of_friends, sum(p1.Score) as sun_of_marks from person p inner join friend f on p.PersonID=f.pid inner join person p1 on f.fid=p1.PersonID group by p.PersonID, p.Name having sum(p1.Score)>100
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;
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
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;
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
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_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 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 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 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 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 ;
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
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 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 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
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 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
/* 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
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 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 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
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;
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
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
MYSQL solution: with get_friends as (select distinct p.personid, p.name, p.score, f.fid from person p join friend f on p.PersonID = f.pid), get_scores as (select distinct personid, name, score from person), get_friends_scores as (select t1.*,t2.name as friend_name, t2.score as friend_score from get_friends t1 join get_scores t2 on t1.fid = t2.PersonID), high_friend_score as (select *, sum(friend_score) over(partition by personid) as total_friend_score from get_friends_scores) select distinct personid, total_friend_score from high_friend_score where total_friend_score > 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!)
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
