IPL Winning Streak - SQL Interview Query 22 | SQL Problem Level "HARD"
HTML-код
- Опубликовано: 30 июл 2024
- 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:
learnsql.com/?ref=thoufiqmoha...
Some FREE blogs to practice SQL queries and Interview questions:
learnsql.com/blog/sql-window-...
learnsql.com/blog/advanced-sq...
learnsql.com/blog/group-by-ex...
learnsql.com/blog/sql-joins-p...
learnsql.com/blog/sql-subquer...
learnsql.com/blog/advanced-sq...
Let's follow the below routine to make the best use of it:
1. Watch the RUclips 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 RUclips video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/blog/30daysqlquer...
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!
U r great tutor.... yesterday I was unable to attend ur live session bcoz of meeting....
Thanks for ur session
Awesome query. enjoying learning.
Good one Bro!
Amazing ❤
Kindly make videos on using mutual funds or stocks data. Please consider my request. It will be more useful to your followers.
Watching ipl along with your video
🙏
RCB -Mujhe kyu toda?
Please,One small session for IPL schedule matches
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)
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');
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
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.
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;
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 :
ruclips.net/video/ygEzGdrKg5k/видео.html
No Comments :) , Haha all are busy in IPL
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