I guess by the end of 30 queries, anyone who is able to understand the 30 query concepts, I guess he can eaily be certified with SQL, It literally covers all topics, how to think logically which concept your weak at and so, we gotta really appreciate this guy for his hardwork and effort, maybe in few days this is will be recorded best yotube channel to learn sql across all nations, Keep Going Sir, Wish you start Python too and Data Science Topics.
Great solution as always! I have a question regarding query efficiency: in the above code you used a correlated subquery which might be problematic when the data is scaled to much larger sizes since correlated subqueries are iterative (the in operator you used will go through the entire dataset for each outer query) compared to standard set based SQL operations like self join. So will it not be better to solve the entire problem only using self joins rather than using correlated subqueries? I tried using only self joins and the code looked much larger than yours as I had to use multiple ctes. But sometimes length of the code doesn't translate to higher optimization. What are your comments on using self join vs correlated subquery? my solution without using the correlated subquery (only self joins): with cte as (select *, row_number() over() as rn from table), cte_left as ( select t1.friend1 as f1, t1.friend2 as f2, t2.friend1 as f3, t2.friend2 as f4, t1.rn as r1, t2.rn as r2 from cte as t1 join cte as t2 on (t1.friend1=t2.friend1 or t1.friend1=t2.friend2) and (t1.rnt2.rn) order by t1.friend1), cte_right as ( select t1.friend1 as f1, t1.friend2 as f2, t2.friend1 as f3, t2.friend2 as f4, t1.rn as r1, t2.rn as r2 from cte as t1 join cte as t2 on (t1.friend2=t2.friend1 or t1.friend2=t2.friend2) and (t1.rnt2.rn) order by t1.friend1), cte_final as ( select c1.f1,c1.f2,c1.f3,c1.f4,c2.f3 as f5,c2.f4 as f6, c1.r1 as new_rn from cte_left as c1 join cte_right as c2 on c1.r1=c2.r1 where (c1.f3=c2.f3 or c1.f3=c2.f4 or c1.f4=c2.f3 or c1.f4=c2.f4) order by new_rn) select friend1,friend2,count(f1) as mutual_friends from cte left join cte_final on cte.rn=cte_final.new_rn group by friend1, friend2;
Interesting point about code optimization. The problem can be solved with multiple joins instead of creating separate joins. with pairs as ( select Friend1 personA, Friend2 personB from Friends union select Friend2, Friend1 from Friends ) select Friend1, Friend2, count(frA.personB) mutual_friends from Friends f join pairs frA on f.Friend1=frA.personA join pairs frB on f.Friend2=frB.personA where frA.personB = frB.personB group by Friend1, Friend2 order by Friend1, Friend2 The catch with this code, though, is that pairs with 0 mutual friends (John,Mary) won't show. This can be easily corrected by making an additional cte of this result and using this cte in an outer join on the original table.
@@gphanisrinivasful i realised that sweet trick of using the union after seeing toufiqs solution too which will help me eliminate two ctes in my code. Thanks for pointing that out.
@@Tusharchitrakar I don't think this approach is all that better than TFQ's, complexity wise. In both the solutions, we're scanning the entire table for friends of each person in the six pairs and sifting out the common ones. Complexity still remains n^2.
@@gphanisrinivasful not true. Using correlated subqueries is analogous to a nested for loop in programming where for each of the outer query we scan the entire table of the inner query which leads to a worst case time complexity of 0(n^2). Again, different database systems have better optimization techniques which can improve this better but I'm not an expert in optimization to know what they are. However, a self join is a set based operation and is not the same as a nested for loop. In programing terms, it is an example of vectorization where there is no iteration happening but it can use more efficient strategies at a lower level for the joins to work where the time complexities may range between 0(nlogn) and 0(n^2). Again, i might be wrong about the specifics, but all in all there is a common notion in SQL that using joins is always more efficient than a correlated subquery but regarding the specifics I'm not sure which is why I had the query. When I read a stack overflow article on the same, it did mention that there is no golden rule on which is better but for larger datasets joins seems better whereas correlated subqueries might get the job done for smaller datasets Unable to paste links here, but just Google search "Troubleshooting correlated subqueries performance in MySQL" and check out the link by minervadb. Also, check the stack overflow article on "how to optimise correlated subquery" for an example on how using joins reduced execution time from 38s to 2s compared to correlated subquery
@@Tusharchitrakar TH-cam seems to block external links in comments for some reason. Thanks for the suggestions, I will look at those articles. This is an interesting topic to look into, how joins work and their time and space complexity.
Using group BY with all_friends as ( select friend1 , friend2 from friends union all select friend2 , friend1 from friends ) select t1.friend1 , t1.friend2 , count(t2.friend2) as mutual_friends from friends as t1 left join all_friends as t2 on t1.friend1 = t2.friend1 and t2.friend2 in ( select t3.friend2 from all_friends as t3 where t1.friend2 = t3.friend1 ) group by t1.friend1 , t1.friend2 order by t1.friend1 ;
-- Using group BY with all_friends as ( select friend1 , friend2 from friends union all select friend2 , friend1 from friends ) select t1.friend1 , t1.friend2 , count(t2.friend2) as mutual_friends from friends as t1 left join all_friends as t2 on t1.friend1 = t2.friend1 and t2.friend2 in ( select t3.friend2 from all_friends as t3 where t1.friend2 = t3.friend1 ) group by t1.friend1 , t1.friend2 order by t1.friend1 ;
I solved it simply using ms sql server I did not use any window function with CTE as (select Friend1,Friend2 from Friends a union All select Friend2 as Friend1,Friend1 as Friend2 from Friends b) select Friends.*, MUTUAL_FRIENDS = (select count(*) from CTE where CTE.Friend1 = Friends.Friend1 and Friend2 IN (select Friend2 from CTE a where Friend1 = Friends.Friend2)) from Friends
Fantastic share as always Thoufik! here's my attempt on MS SQL server: =================================================== with friends_c as (select Friend1 as [Person], Friend2 as [Friends] from Mutual_Friends union select Friend2, Friend1 from Mutual_Friends), F1_friends as (select * from Mutual_Friends f1 left join friends_c f2 on f1.Friend1 = f2.[Person]), F2_friends as (select * from Mutual_Friends f1 left join friends_c f2 on f1.Friend2 = f2.[Person]) select f3.Friend1, f3.Friend2, COUNT(f1.Friends) from F1_friends f1 join F2_friends f2 on f1.Friend1 = f2.Friend1 and f1.Friend2 = f2.Friend2 and f1.Friends = f2.Friends right join Mutual_Friends f3 on f3.Friend1 = f1.Friend1 and f3.Friend2 = f1.Friend2 group by f3.Friend1, f3.Friend2 order by 1 ====================================================
This is quite challenging problem, Solved using MYSQL with cte as (SELECT Friend1,Friend2 FROM friends union SELECT Friend2,Friend1 FROM friends) ,cte1 as (select c.Friend1,f.Friend2, count(c.Friend2) as mutual from friends f left join cte c on c.Friend1=f.Friend1 left join cte c1 on f.Friend2=c1.Friend1 where c.Friend2=c1.Friend2 group by c.Friend1,f.Friend2) ,cte2 as (select * from cte1 union SELECT *,0 as mutaul FROM tfq.friends) select Friend1,Friend2,max(mutual) as mutual from cte2 group by Friend1,Friend2
with cte as ( select friend1, friend2 from Friends union all select friend2, friend1 from Friends order by 1 ) select distinct f1.*, count(cte.friend2) over (partition by f1.friend1, f1.friend2) from friends f1 left join cte on f1.friend1 = cte.friend1 and cte.friend2 in (select c.friend2 from cte c where f1.friend2 = c.friend1 )
I guess by the end of 30 queries, anyone who is able to understand the 30 query concepts, I guess he can eaily be certified with SQL, It literally covers all topics, how to think logically which concept your weak at and so, we gotta really appreciate this guy for his hardwork and effort, maybe in few days this is will be recorded best yotube channel to learn sql across all nations, Keep Going Sir, Wish you start Python too and Data Science Topics.
Thanks Sandeep.. lovely words man.. appreciate it ❤️
. @techTFQ You've earned those words, buddy..
Thanks Craig ☺️
Really... it's true, and i am following the 30-day challenge.
I hope we get more such challenges on your channel, this is very helpful. Keep up the good work.
Amazing videos it's really helpful to the developers❤
Excellent problem, i spent around an hour to get this done
I had to come back to this problem like 3 times and it finally made sense 😅
Great explanation
this problem was really interesting, thank you.. i solved it❤
Great solution as always! I have a question regarding query efficiency: in the above code you used a correlated subquery which might be problematic when the data is scaled to much larger sizes since correlated subqueries are iterative (the in operator you used will go through the entire dataset for each outer query) compared to standard set based SQL operations like self join.
So will it not be better to solve the entire problem only using self joins rather than using correlated subqueries? I tried using only self joins and the code looked much larger than yours as I had to use multiple ctes. But sometimes length of the code doesn't translate to higher optimization.
What are your comments on using self join vs correlated subquery?
my solution without using the correlated subquery (only self joins):
with cte as
(select *, row_number() over() as rn from table),
cte_left as
(
select t1.friend1 as f1, t1.friend2 as f2, t2.friend1 as f3, t2.friend2 as f4, t1.rn as r1, t2.rn as r2
from cte as t1 join cte as t2
on (t1.friend1=t2.friend1 or t1.friend1=t2.friend2)
and (t1.rnt2.rn) order by t1.friend1),
cte_right as
(
select t1.friend1 as f1, t1.friend2 as f2, t2.friend1 as f3, t2.friend2 as f4, t1.rn as r1, t2.rn as r2
from cte as t1 join cte as t2
on (t1.friend2=t2.friend1 or t1.friend2=t2.friend2)
and (t1.rnt2.rn) order by t1.friend1),
cte_final as
(
select c1.f1,c1.f2,c1.f3,c1.f4,c2.f3 as f5,c2.f4 as f6, c1.r1 as new_rn
from cte_left as c1 join cte_right as c2 on c1.r1=c2.r1
where (c1.f3=c2.f3 or c1.f3=c2.f4 or c1.f4=c2.f3 or c1.f4=c2.f4) order by new_rn)
select friend1,friend2,count(f1) as mutual_friends
from cte left join cte_final on cte.rn=cte_final.new_rn
group by friend1, friend2;
Interesting point about code optimization. The problem can be solved with multiple joins instead of creating separate joins.
with pairs as (
select Friend1 personA, Friend2 personB
from Friends
union
select Friend2, Friend1
from Friends
)
select Friend1, Friend2, count(frA.personB) mutual_friends
from Friends f
join pairs frA
on f.Friend1=frA.personA
join pairs frB
on f.Friend2=frB.personA
where frA.personB = frB.personB
group by Friend1, Friend2
order by Friend1, Friend2
The catch with this code, though, is that pairs with 0 mutual friends (John,Mary) won't show. This can be easily corrected by making an additional cte of this result and using this cte in an outer join on the original table.
@@gphanisrinivasful i realised that sweet trick of using the union after seeing toufiqs solution too which will help me eliminate two ctes in my code. Thanks for pointing that out.
@@Tusharchitrakar I don't think this approach is all that better than TFQ's, complexity wise. In both the solutions, we're scanning the entire table for friends of each person in the six pairs and sifting out the common ones. Complexity still remains n^2.
@@gphanisrinivasful not true. Using correlated subqueries is analogous to a nested for loop in programming where for each of the outer query we scan the entire table of the inner query which leads to a worst case time complexity of 0(n^2). Again, different database systems have better optimization techniques which can improve this better but I'm not an expert in optimization to know what they are. However, a self join is a set based operation and is not the same as a nested for loop. In programing terms, it is an example of vectorization where there is no iteration happening but it can use more efficient strategies at a lower level for the joins to work where the time complexities may range between 0(nlogn) and 0(n^2).
Again, i might be wrong about the specifics, but all in all there is a common notion in SQL that using joins is always more efficient than a correlated subquery but regarding the specifics I'm not sure which is why I had the query. When I read a stack overflow article on the same, it did mention that there is no golden rule on which is better but for larger datasets joins seems better whereas correlated subqueries might get the job done for smaller datasets
Unable to paste links here, but just Google search "Troubleshooting correlated subqueries performance in MySQL" and check out the link by minervadb. Also, check the stack overflow article on "how to optimise correlated subquery" for an example on how using joins reduced execution time from 38s to 2s compared to correlated subquery
@@Tusharchitrakar TH-cam seems to block external links in comments for some reason. Thanks for the suggestions, I will look at those articles. This is an interesting topic to look into, how joins work and their time and space complexity.
Waiting for your upload daily😅
👍
Using group BY
with all_friends as (
select friend1 , friend2
from friends
union all
select friend2 , friend1
from friends
)
select t1.friend1 , t1.friend2 , count(t2.friend2) as mutual_friends
from friends as t1
left join all_friends as t2
on t1.friend1 = t2.friend1
and t2.friend2 in (
select t3.friend2
from all_friends as t3
where t1.friend2 = t3.friend1
)
group by t1.friend1 , t1.friend2
order by t1.friend1 ;
Please adjust ur mic
Sound is too low
I don’t see any issue. Just checked again and It’s very loud and clear so plz check your speakers
God of SQL ❤
-- Using group BY
with all_friends as (
select friend1 , friend2
from friends
union all
select friend2 , friend1
from friends
)
select t1.friend1 , t1.friend2 , count(t2.friend2) as mutual_friends
from friends as t1
left join all_friends as t2
on t1.friend1 = t2.friend1
and t2.friend2 in (
select t3.friend2
from all_friends as t3
where t1.friend2 = t3.friend1
)
group by t1.friend1 , t1.friend2
order by t1.friend1 ;
I solved it simply using ms sql server I did not use any window function
with CTE as (select Friend1,Friend2 from Friends a
union All
select Friend2 as Friend1,Friend1 as Friend2 from Friends b)
select
Friends.*,
MUTUAL_FRIENDS = (select
count(*)
from
CTE
where
CTE.Friend1 = Friends.Friend1
and Friend2 IN (select Friend2
from CTE a
where Friend1 = Friends.Friend2))
from
Friends
sir phli baar me kuch samjh nhi aaya, hopefully will get it in next round
Hi , how yo contact you. I want to take help in person ❤
Fantastic share as always Thoufik!
here's my attempt on MS SQL server:
===================================================
with friends_c as (select Friend1 as [Person], Friend2 as [Friends] from Mutual_Friends
union
select Friend2, Friend1 from Mutual_Friends),
F1_friends as (select *
from Mutual_Friends f1
left join friends_c f2 on f1.Friend1 = f2.[Person]),
F2_friends as (select *
from Mutual_Friends f1
left join friends_c f2 on f1.Friend2 = f2.[Person])
select f3.Friend1, f3.Friend2, COUNT(f1.Friends)
from F1_friends f1
join F2_friends f2
on f1.Friend1 = f2.Friend1 and f1.Friend2 = f2.Friend2 and f1.Friends = f2.Friends
right join Mutual_Friends f3 on f3.Friend1 = f1.Friend1 and f3.Friend2 = f1.Friend2
group by f3.Friend1, f3.Friend2
order by 1
====================================================
This is quite challenging problem, Solved using MYSQL
with cte as (SELECT Friend1,Friend2
FROM friends
union
SELECT Friend2,Friend1
FROM friends)
,cte1 as (select c.Friend1,f.Friend2, count(c.Friend2) as mutual
from friends f
left join cte c on c.Friend1=f.Friend1
left join cte c1 on f.Friend2=c1.Friend1
where c.Friend2=c1.Friend2
group by c.Friend1,f.Friend2)
,cte2 as (select * from cte1
union
SELECT *,0 as mutaul FROM tfq.friends)
select Friend1,Friend2,max(mutual) as mutual from cte2 group by Friend1,Friend2
WHat is tfq.friends?
@@DataAnalyst251 Name of database followed by table name
ANYWAY TQ, using multple CTEs seems easier @@Satish_____Sharma
with cte as (
select friend1, friend2 from Friends
union all
select friend2, friend1 from Friends
order by 1
)
select distinct f1.*, count(cte.friend2) over (partition by f1.friend1, f1.friend2) from friends f1
left join cte on f1.friend1 = cte.friend1 and cte.friend2 in (select c.friend2 from cte c where f1.friend2 = c.friend1 )