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...
Пікірлер: 135
@apurvasaraf58285 ай бұрын
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
@HARSHRAJ-wz2rp8 күн бұрын
Thank you so much aporva
@bukunmiadebanjo96846 ай бұрын
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
@nakulbageja2232 Жыл бұрын
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
@rawat72039 ай бұрын
Modi hai to munkin hai Jai Siya Ram
@yashmishra406911 ай бұрын
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
@vinodsetiyar2 жыл бұрын
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?
@sureshraina321 Жыл бұрын
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
@suriyas6338 Жыл бұрын
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
@praveensinghrathore45422 жыл бұрын
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;
@ankitbansal62 жыл бұрын
Thank you 😊. Appreciate the time spent.
@snehachoudhary93672 жыл бұрын
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?
@rawat72039 ай бұрын
Modi hai to munkin hai Jai Siya Ram
@sunnygoswami53582 жыл бұрын
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
@ankitbansal62 жыл бұрын
Good one Sunny. Thanks for sharing 😊
@hustlerguy60912 жыл бұрын
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.
@ls47295 Жыл бұрын
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 ;
@GauravKumar-ps7tb Жыл бұрын
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
@biswajitpradhan61212 жыл бұрын
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
@anujgupta-lc1md2 жыл бұрын
create table and insert also please it will save time and focus on logic. keep going good collection of questions.
@ankitbansal62 жыл бұрын
Hi Anuj, I have put the link to the dataset in the description box.
@nishushroff96562 жыл бұрын
Very well explained...👍❤
@joerokcz Жыл бұрын
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
@arunsundar37394 ай бұрын
beautifully explained, thank you very much :)
@SachinKumarHS4 ай бұрын
@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
@vaibhavverma13402 жыл бұрын
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
@ankitbansal62 жыл бұрын
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.
@vaibhavverma13402 жыл бұрын
@@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!!
@ankitbansal62 жыл бұрын
Exactly. We are checking friend score only right. Persons whose friend score more than certain value. That is friend score only 🙂
@litheshraju52072 жыл бұрын
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
@ankitbansal62 жыл бұрын
Thanks for posting 👏
@ankitbiswas83802 жыл бұрын
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
@ankitbansal62 жыл бұрын
You mean in the select ?
@rashmidutta71512 жыл бұрын
@@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
@Data.scientistt Жыл бұрын
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
@ankitbansal6 Жыл бұрын
Brilliant. Keep going 😊
@vikhyatjalota2213Ай бұрын
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
@abhishek_grd2 жыл бұрын
Thanks for the video.
@ankitbansal62 жыл бұрын
🙏
@akashdeep5088 Жыл бұрын
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
@user-ur6ot2xl8y Жыл бұрын
wonderful explanation!
@ankitbansal6 Жыл бұрын
Glad you liked it
@Vaibha2938 ай бұрын
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
@anshikamungiya53502 жыл бұрын
I am not able to understand why you are joining on f.friendid=p.personid, shouldn't it be f.personid=p.personid?
@ankitbansal62 жыл бұрын
To get scores for each of the friend
@prudviraj75866 ай бұрын
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 ;
@mithunnambiar14332 жыл бұрын
Hi Sir! we practise on oracle as well so please have the schema posted in other than the dataset.
@ankitbansal62 жыл бұрын
Sure .
@techmania979 Жыл бұрын
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
@vutv57428 ай бұрын
Completed ❤
@dipanjan932 жыл бұрын
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
@ankitbansal62 жыл бұрын
Thanks for posting 👏
@parthchadha967610 ай бұрын
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.
@rahulmehla20143 ай бұрын
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
@HelloSrikanth2 жыл бұрын
Nice video bro
@ankitbansal62 жыл бұрын
Thank you 😊
@kanchankumar33552 жыл бұрын
Great explanation
@ankitbansal62 жыл бұрын
Thank you 😊
@pavanreddy57242 ай бұрын
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;
@kartikeyasingh27983 күн бұрын
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
@srishtidixitsd4 ай бұрын
If anyone could let me know why here join is on friend id and person id?
@sandipsarkar4555 Жыл бұрын
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
@abhimistry92262 жыл бұрын
thanks
@amazingriknow5986 Жыл бұрын
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;
@pavitrashailaja8502 жыл бұрын
Nice video🙏🏻
@ankitbansal62 жыл бұрын
Thank you 😊
@jasonakash4369 Жыл бұрын
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;
@sureshpathak20082 жыл бұрын
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;
@mohammadabdullahansari63142 жыл бұрын
It'll give empty table
@uttamshukla4768 Жыл бұрын
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
@Aks-472 жыл бұрын
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;
@ankitbansal62 жыл бұрын
Thanks for posting 👏
@vyabinivenkatesan7839 Жыл бұрын
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
@vinothvk27119 ай бұрын
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
@sreejitchakraborty65752 жыл бұрын
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);
@SudhirKumar-rl4wt2 жыл бұрын
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
@Ankitatewary-q6w12 күн бұрын
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 ;
@gothams11952 жыл бұрын
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
@sevugakumar14954 ай бұрын
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 ;
@yogamayaa4 ай бұрын
Where to find the script for this
@sngv13 ай бұрын
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
@sagarsaini54472 ай бұрын
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
@SumanGhosh-vn3tx2 жыл бұрын
Can we get the data, so that we can practice?
@ankitbansal62 жыл бұрын
Please download data from below link: drive.google.com/drive/folders/1Dc81McsB4lp1JUIwssDmmOaw6Z7rBK8T?usp=sharing
@SumanGhosh-vn3tx2 жыл бұрын
@@ankitbansal6 thanks a lot
@ajithjoj Жыл бұрын
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
@anishchhabra60857 ай бұрын
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;
@Vaibha2936 ай бұрын
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
@Ankushsharma-mk4ij9 ай бұрын
CREATE TABLE person( PersonID int, Name VARCHAR(1000), Email VARCHAR(500), Score int );
@himanshugarg20607 ай бұрын
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
@arslanmuhammad419010 ай бұрын
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;
@user-zx1ii2cx2j Жыл бұрын
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!)
@ganeshgardas9129 Жыл бұрын
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;
@rajunaik8803 Жыл бұрын
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
@yatinshekhar78711 ай бұрын
11/142
@anirvansen2941 Жыл бұрын
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;
@waqarsarwar7012 Жыл бұрын
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
@vandanaK-mh9zo Жыл бұрын
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
@shwetasaini6892 Жыл бұрын
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
@naveenkumar-tg2lr2 жыл бұрын
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
@ankitbansal62 жыл бұрын
Thanks for posting 👏
@Abhilash_Jose24 күн бұрын
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;
@vandanaK-mh9zo7 ай бұрын
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 ;
@arjitakashyap816910 ай бұрын
;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 )
@ls47295 Жыл бұрын
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 ;
@LearnYouAndMe4 ай бұрын
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
@oorvimathur9807 Жыл бұрын
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
@rajkumarrajan805910 ай бұрын
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
@indergaming3053 Жыл бұрын
/* 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
@srinushanigarapu83299 ай бұрын
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
@apurvasaraf58285 ай бұрын
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
@deautomate Жыл бұрын
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
@addhyasumitra90Ай бұрын
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
@sameerpandey21409 ай бұрын
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
@abubakaralikhan-8614 Жыл бұрын
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
@Mathematica1729 Жыл бұрын
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
@shantanusaxena68638 ай бұрын
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
@user-ju8en2wl3p Жыл бұрын
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
@saiswaroop35707 ай бұрын
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
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
@anandahs60788 ай бұрын
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;
@grim_rreaperr2 ай бұрын
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;