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');
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;
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.
@@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.
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
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
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
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
So good to understand, because you explained so very well. Thanks a lot from yhe learners community
U r great tutor.... yesterday I was unable to attend ur live session bcoz of meeting....
Thanks for ur session
Kindly make videos on using mutual funds or stocks data. Please consider my request. It will be more useful to your followers.
Awesome query. enjoying learning.
Hernandez Shirley Wilson Kenneth Lewis Margaret
Amazing ❤
Good one Bro!
Please,One small session for IPL schedule matches
additional test cases seems personal 🤣🤣
Watching ipl along with your video
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');
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;
🙏
Sir, can't we use dense_rank instead of row_number function ?
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)
Perhaps I'm missing something, but how would this apply to an on the job query?
This, I mean maybe this exercises are great for your mind, but they don't seem to be practical in a real life
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.
@@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.
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
RCB -Mujhe kyu toda?
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
PySpark Version of this problem :
ua-cam.com/video/ygEzGdrKg5k/v-deo.html
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
No Comments :) , Haha all are busy in IPL
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