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

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the 22nd video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.

Learn and Practice SQL on LearnSQL platform below:

Some FREE blogs to practice SQL queries and Interview questions:

Let's follow the below routine to make the best use of it:
1. Watch the YouTube video (first half) to understand the problem statement.

2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.

3. Share your solution on Discord and discuss different solutions and issues on my Discord server.

4. Watch the second half of my YouTube video to find my solution to the problem.

5. Share it with your contacts and spread the knowledge.

DOWNLOAD the Dataset from below:

Timeline:
00:00 Intro
00:12 Understanding Problem Statement
04:34 Solution to the SQL Problem

Thanks for participating in this challenge!

Good luck and Happy Learning!

[arunjeshwanth]

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

[balukrishna]

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

[gopinadhvarma]

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

[ehsanul]

Please, One small session for IPL schedule matches

[ameygoesgaming]

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)

[newenglandnomad]

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

[shinejohnson]

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');

[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;

[shivinmehta]

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

[vikramjitsingh]

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

[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