IPL Winning Streak - SQL Interview Query 22 | SQL Problem Level "HARD"

[@arunjeshwanth4999]

U r great tutor.... yesterday I was unable to attend ur live session bcoz of meeting.... Thanks for ur session

[@niravnishith7051]

Great way of explaining ,Big Fan

[@webdeveloper-q1i]

So good to understand, because you explained so very well. Thanks a lot from yhe learners community

[@AkkKrni]

Awesome query. enjoying learning.

[@balukrishna2581]

Kindly make videos on using mutual funds or stocks data. Please consider my request. It will be more useful to your followers.

[@iamkiri_]

Good one Bro!

[@JayPatel-n8b]

Amazing ❤

[@SylviaFerguson-u8g]

Hernandez Shirley Wilson Kenneth Lewis Margaret

[@raishabanu]

Watching ipl along with your video

[@Hsalz]

🙏

[@ehsanul559]

Please,One small session for IPL schedule matches

[@gopinadhvarma5455]

Sir, can't we use dense_rank instead of row_number function ?

[@giriraj191]

additional test cases seems personal 🤣🤣

[@shinejohnson777]

UPDATE ipl_results set result= case when home_team='Royal Challengers Bangalore' then away_team else home_team end where match_no in(select match_no from ipl_results where result='Royal Challengers Bangalore');

[@vikramjitsingh6769]

Mysql people here u go - with cte as (select home_team, away_team, result, dates from ipl_results union all select away_team,home_team, result, dates from ipl_results) select home_team , max(cou) from(select *, count(1) over(partition by row2, home_team) as cou from (select *, (row1 - row_number() over(partition by home_team order by dates)) as row2 from (select *, row_number() over(partition by home_team order by dates ) as row1 from cte)x where home_team = result)x order by home_team, dates)x group by home_team order by max(cou) desc

[@newenglandnomad9405]

Perhaps I'm missing something, but how would this apply to an on the job query?

[@mrbartuss1]

This, I mean maybe this exercises are great for your mind, but they don't seem to be practical in a real life

[@techTFQ]

Imagine a case where you need to flag a customer for fraudulent transactions and one of the requirement is to see if there were transactions across 3 or 5 continuous days with some pattern then the logic used in this query could be helpful. OR imagine, you need to figure out 5 consecutive days where there were least or most orders/revenue during a year then the logic used in this query could be used. Idea is to learn how to solve different types of problems using SQL which gives you an idea on all the different ways to use SQL which will eventually help at work.

[@gphanisrinivasful]

@@techTFQ We already had a problem (#17) where we find the login streak for users, which was quite a practical, useful one. This problem is similar to that, except the consecutive matches aren't on consecutive days. Finding a streak is a practical problem indeed.

[@Tusharchitrakar]

My take on this before seeing your solution (assumption is that no result does not end the streak) and I notice that it is similar except I did not use joins but the grouping is based on similar logic: -- first cte to group winning/no_result as 1 and losing as 0 with cte1 as ( select match_no, result as team, 1 as result from ipl_results where result'No Result' UNION ALL select match_no, case when result=home_team then away_team else home_team end as team, 0 as result from ipl_results where result'No Result' UNION ALL select match_no, home_team, 1 as result from ipl_results where result='No Result' UNION ALL select match_no, away_team, 1 as result from ipl_results where result='No Result'), -- second cte to give a match_no to all matches regardless of winning/losing/no result cte2 as ( select *, row_number() over(partition by team order by match_no) as specific_match_no from cte1 order by team, match_no), -- third cte to give a match_no to only the winning matches cte3 as ( select team, specific_match_no, row_number() over(partition by team order by specific_match_no) as winning_match_no from cte2 where result=1), -- grouping island technique to group based on successive winning match streaks cte4 as ( select team,specific_match_no-winning_match_no as group_no, count(1) as winning_streak from cte3 group by team,2) -- final query to output the winning streaks select team, max(winning_streak) as max_winning_streak from cte4 group by team order by 2 desc;

[@YASHHHHHHHHHHHHHH]

RCB -Mujhe kyu toda?

[@DEwithDhairy]

PySpark Version of this problem : kzbin.info/www/bejne/r5ioq3qap7Caa80

[@ARJUNKRISHNA-mq3wj]

so how can we come up with this solution in 30 minutes

[@sahilkadu5409]

They did RCB dirty😂😂

[@ameygoesgaming8793]

Instead of last cte_final, can't we do, select team, max(cnt) FROM( Select team, diff, count(diff) cnt FROM cte_teams GROUP BY team, diff)

[@shivinmehta7368]

with base as ( select distinct home_team as team from ipl_results union select distinct away_team as team from ipl_results ) select team,max(streak) as longest_streak from ( select team , grp , sum(flag) as streak from (select team,dates,result,case when result=team then 1 else 0 end as flag, sum(case when result=team then 1 else 0 end ) over (partition by team order by dates asc) as cflag, row_number() over(partition by team order by dates asc) as rn, row_number() over(partition by team order by dates asc) - sum(case when result=team then 1 else 0 end ) over (partition by team order by dates asc) as grp from base a left join ipl_results b on a.team=b.home_team or a.team=b.away_team )x where flag=1 group by 1,2 ) y group by 1 order by 2 desc

[@HeyyRomii]

No Comments :) , Haha all are busy in IPL

[@Alexpudow]

with a as ( select dates, home_team team, result from ipl_results union all select dates, away_team team, result from ipl_results), b as (select team ,row_number() over(partition by team order by dates) gn ,case when team = result then 1 else 0 end wg from a), c as (select team, gn - row_number() over (partition by team order by gn) sw from b where wg=1) select distinct team, max(count(sw)) over(partition by team) max_ws from c group by team, sw union all select team, max(wg) max_ws from b group by team having max(wg) = 0 order by 2 desc

[@Satish_____Sharma]

My solution using MYSQL with cte as (SELECT home_team as teams FROM tfq.ipl_results union SELECT away_team FROM tfq.ipl_results) ,cte1 as (select dates,concat(home_team ,' Vs ', away_team) as Matches,teams,result, row_number() over (partition by teams order by dates) as rw from cte left join ipl_results ipl on cte.teams=ipl.home_team or cte.teams=ipl.away_team order by teams,dates), cte2 as (select *,rw-row_number() over (partition by teams order by dates) as rw1 from cte1 where teams=result), cte3 as (select teams,count(rw1) as streak from cte2 group by teams,rw1) select cte.teams as teams,coalesce(max(streak),'0 Matches Won') as streak from cte left join cte3 on cte.teams=cte3.teams group by cte.teams order by streak desc