Complex SQL 6 | Scenario based on join, group by and having clauses | SQL Interview Question

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:

[apurvasaraf]

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

[bukunmiadebanjo]

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

[nakulbageja]

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

[yashmishra]

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

[sureshraina]

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

[vikhyatjalota]

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

[vinodsetiyar]

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?

[praveensinghrathore]

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;

[sunnygoswami]

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

[suriyas]

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

[litheshraju]

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

[Vaibha]

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

[biswajitpradhan]

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

[pavanreddy]

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;

[snehachoudhary]

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?

[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

[rahulmehla]

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

[SachinKumarHS]

@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

[vinothvk]

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

[arunsundar]

beautifully explained, thank you very much :)