Complex SQL Query 1 | Derive Points table for ICC tournament

[@ankitbansal6]

Master the art of SQL with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch.
www.namastesql.com/course-detail/think-sql-go-from-zero-to-hero-english
The course contains 2 portfolio projects and a bonus session on Tableau.
50+ interview problems to crack any SQL INTERVIEW.

[@adityabaha]

Your videos cleared my SQL concepts & boosted my confidence 100 times. Because of that, I was able to crack a job as Data Engineer @ Apple. Thank you from the bottom of my heart ❤

[@ankitbansal6]

Time to celebrate,🎉🎉 Many congratulations 👏

[@rishav144]

@@ankitbansal6 awesome videos 🔥

[@kunalr_ai]

Dost hai kya tu Ankit ka ...jhoot ki hadd hai..iske video se tune apple main selection karwa liya baaki sab to c baithe hai yaha par

[@rishav144]

@@kunalr_ai bhai , usne mehnat kiya hoga ...Then , surely possible ....Data Engineer role mei SQL, Spark , Hadoop , Python , Hive , AWS , Data Warehousing etc ke questions aate hai ......Usko accha knowledge hoga , then why not possible in Apple .....

[@kunalr_ai]

@@rishav144 naam se pata chal rha hai ...bansal ka to samj aata hai ye Amazon main hoga par ye bahadur ye log to reservation ki job karne walo main se hai inpe talent kaha .. kitna bhi padh le ye lekin kabhi bhi ye top position par nahi jaayega...waha par sirf general waale Jaa sakte hai

[@user-rr9lo4wc8g]

Surprised to see that chatGPT is able to solve this question in just 2 sec. I just gave the input table name, columns, output column names and it understood the context and gave the output perfectly. It gave 3 other approaches too when asked. ChatGPT is kinda scary at times.

[@shafiq147]

But during interview interviewer not allowed to use chatgpt

[@Howto-ty4ru]

Have not found any other channel with such practical relatable, unique examples

[@BismarckWangkhem]

Great question Ankit! thanks for sharing. Here is my attempt:
with cte as
(select team_1,case when team_1 = winner then 1 else 0 end win_flag
from icc_world_cup
union all
select team_2, case when team_2 = winner then 1 else 0 end win_flag
from icc_world_cup)
select t.*, (t.number_of_match_played - number_of_win) number_of_loss from
(select team_1 team, count(*) number_of_match_played, sum(win_flag) number_of_win from cte
group by team_1)t

[@shatirdimag1011]

best channel for leanring complex sql thanks Ankit Sir

[@sachinray3019]

Great video and explanation !
Tried extended solution for draw.
select team_name, count(1) as matches, sum(flag=1) as wins,sum(flag=0) as loss,sum(flag='D') as draw from
(select team_1 as team_name, case when team_1=winner then 1 when winner='DRAW' then 'D' else 0 end as flag from icc_wc
union all
select team_2 as team_name, case when team_2=winner then 1 when winner='DRAW' then 'D' else 0 end as flag from icc_wc)A
group by team_name
order by 3 desc;

[@akashm1027]

I got error Conversion failed when converting the varchar value 'D' to data type int. any soln

[@youcanyouwill2004]

​@@akashm1027 I think if draw is considered each match can have 2 points on win 1 point on draw and 0 point on loss. Then it will be simple..

[@mohitmotwani9256]

I solved the question using two left joins. The solution in the video just blew my mind. I dont know what are the chances to get selected in an interview if you write less optimized solution whereas interviewer is expecting a highly optimized solution.
Thanks for the video!

[@ankitbansal6]

During an interview solving a problem is more important . Giving an optimized approach is icing on the cake 🍰

[@MubarakAli-qs9qq]

Did u get placed somewhere

[@mohitmotwani9256]

@@MubarakAli-qs9qq I was actually able to convert 5 offers including 2 product based companies

[@mohitmotwani9256]

@@MubarakAli-qs9qq I was actually able to convert 5 offers including 2 Product based companies

[@mohitmotwani9256]

@@MubarakAli-qs9qq I was actually able to convert 5 offers

[@rudranilgupta9573]

Great video, mind blowing ! This is the video which generated a lot of interest for me regarding SQL. Even though I worked for around 5 years in SQL and saw production-level huge queries, but this is something I never thought was possible !

[@ankitbansal6]

Glad you enjoyed it!

[@RohitKumar-zm3nw]

With CTE AS (
Select T1.Team_Names, COALESCE(T2.no_of_Wins,0) AS no_of_wins from
(Select Team_1 AS Team_Names
From icc_world_cup
UNION
Select Team_2
from icc_world_cup) T1
LEFT JOIN
(Select Winner, Count(*) AS no_of_Wins from icc_world_cup Group by Winner) T2
ON T1.Team_Names =T2.Winner),
CTE1 AS (
Select Team_1 AS Team_Name1, COUNT(*) AS Matches_Played From
(Select Team_1 from icc_world_cup UNION ALL Select Team_2 from icc_world_cup) T
Group by Team_1),
CTE2 AS (
Select CTE.Team_Names, CTE1.Matches_Played, CTE.no_of_Wins
from CTE JOIN CTE1 ON CTE.Team_Names=CTE1.Team_Name1)
Select CTE2.Team_Names, CTE2.Matches_Played, CTE2.no_of_Wins, (CTE2.Matches_Played-CTE2.no_of_Wins) AS no_of_losses
from CTE2

[@anthonychilaka]

Day 1
Saw this post on LinkedIn yesterday and plan on making each day of the remining 47days count. Thank Ankit for your giving back to the data community

[@nilanjanpaul2723]

Thank you for the detailed explanation.
My solution -
with all_team as(select team_1, case when team_1 = winner then 1 else 0 end as no_of_wins1
from icc_world_cup
union all
select team_2, case when team_2 = winner then 1 else 0 end as no_of_wins1
from icc_world_cup)
select team_1 as team, count(team_1) as no_of_matches_played,
coalesce( sum(case when no_of_wins1 =1 then 1 end ), 0 ) as no_of_wins,
coalesce( sum(case when no_of_wins1 =0 then 1 end ), 0 ) as no_of_losses
from all_team
group by team_1
order by no_of_wins desc, no_of_losses desc

[@makarsh29]

just replaced subquery with cte

[@BhanuGupta-x2j]

Very nice question Ankit Ji:
I tried same with CTE
WITH teams AS
(
SELECT team1 AS Team_Name, CASE WHEN team1 = Winner THEN 1 END As Win_Flag
FROM icc_world_cup
UNION ALL
SELECT team2 AS Team_Name, CASE WHEN team2 = Winner THEN 1 END As Win_Flag
FROM icc_world_cup
)
SELECT Team_Name, COUNT(Team_Name) AS Matches_played, COUNT(Win_Flag) AS no_of_wins, COUNT(Team_Name) - COUNT(Win_Flag) AS no_of_losses
FROM teams
GROUP BY Team_Name
ORDER BY no_of_wins DESC;

[@venkateshraja3994]

Hi Ankit, Thanks for this excellent video. Your approach to this problem statement is great! Thanks again.
select a.*, (Matches_Played - no_of_wins) as no_of_losses
from(select team ,count(1) as Matches_Played, nvl(min(c.no_of_wins),0) as no_of_wins
from(select Team_1 as team from icc_world_cup
union all
select Team_2 as team from icc_world_cup
) a ,
icc_world_cup b,
(select winner,count(1) as no_of_wins from icc_world_cup group by winner) c
where a.team = B.Team_1(+)
and a.team = c.winner(+)
group by team
) a
order by 3 desc

[@bodaddy6771]

I solved this using CTE which is something i dont know why i do very often now
I could get the final answer using this but i really liked your solution it was neat and looked fast

[@milindzuge906]

Nice Ankit, my approach will be like this...
with a as (
select team_1 as team_name,count(1) as num from icc_world_cup group by team_1
union all
select team_2 as team_name,count(1) as num from icc_world_cup group by team_2),
b as (select winner as team_name,count(1) as no_of_wins from icc_world_cup group by winner),
c as (select team_name,sum(num) as matches_played from a group by team_name)
select c.team_name,c.matches_played,isnull(b.no_of_wins,0) as no_of_wins,c.matches_played-isnull(b.no_of_wins,0) as no_of_losses
from c left join b on c.team_name = b.team_name;

[@SudhirKumar-rl4wt]

with temp as (
select team1,winner from input
union all
select team2,winner from input
) select team1,
count(*) no_of_matches_played,
sum(case when winner = team1 then 1 else 0 end) wins,
sum(case when winner team1 then 1 else 0 end) losses
from temp
group by team1

[@ankitbansal6]

Looks good 😊

[@Arvenab_Nayak-5]

Brother don't require case statement for team2 ? could do explain me the reason ?

[@saikumar_andhavarapu]

​@@Arvenab_Nayak-5 because he unioned both columns and make a single column named team1.

[@Arvenab_Nayak-5]

@@saikumar_andhavaraputhank you Yes Understood dada
Alias mandatory for after SQL sub query while Ankit sir use Alias as A after sub query ? Dada can you explain why we use A

[@rajeswarynadarajan8347]

Hi, what mean by temp??

[@rakeshchaudhary3055]

Great Video, Ankit.
Here is another approach that provides the expected output
With matches_played as (
select country,count(country) matches_played from(
SELECT Team_1 country FROM icc_world_cup
union all
SELECT Team_2 country FROM icc_world_cup) a group by country),
matches_won as (select winner country,count(winner) matches_won from icc_world_cup group by winner),
matches_played_won as (select p.country country,matches_played,ifnull(matches_won,0) matches_won
from matches_played p left join matches_won w
on p.country=w.country)
select *, (matches_played-matches_won) matches_lost from matches_played_won order by matches_won desc;

[@user-nc1oz9jr1e]

I really appreciate and thank you for your brilliant effort in making all these contents on SQL. This SQL query is looks stunning and the way you approach in deriving expected output is really excellent and marvelous. people who really wanted make carrier out in SQL I would direct them to this place. HATS OFF ANKIT bro and as always surprise us with lot more contents

[@ankitbansal6]

Great job

[@swethathiruppathy9973]

Thank you sir,for all your videos
hereby sharing my solution
with cte as (select team_1 as team,winner from icc_world_cup
union all
select team_2 as team,winner from icc_world_cup )
,cte2 as (
select *,case when team=winner then 1 else 0 end as win_flag,
count(*) over (partition by team ) as noOfmatches from cte)
select team,noOfmatches, sum(win_flag)as no_of_wins,noOfmatches-sum(win_flag) as no_of_los
from cte2
group by team
order by no_of_wins desc, no_of_los desc

[@subramaniankrishnamoorthy1906]

Before watching your solution, i did, and thanks a lot for all your videos.
with cte as
(
select team_1 from icc_world_cup
union all
select team_2 from icc_world_cup
),cte1 as
(
select team_1 as Team_Name,count(1) as Matches_Played from cte
group by team_1
)
,cte2 as
(
select Team_Name,Matches_Played, count(winner) No_of_wins from cte1 a
left join icc_world_cup b on a.Team_Name=b.winner
group by Team_Name,matches_played
)
select a.*,a.Matches_Played-a.No_of_wins as No_of_losses from cte2 a

[@akashkamble9902]

Thanks for this amazing questions learnt a lot, here is my query for draw case.
SELECT team_name , count(team_name) as noOfmatchPlayed,sum(noOfWins) as noOfWins,
((count(team_name) - sum(noOfWins)) - sum(noOfDraws)) as noOfLoss,
sum(noOfDraws) as noOfDraws
from (
select team_1 as team_name, case when team_1=winner then 1 else 0 end as noOfWins,
case when winner='Draw' then 1 else 0 end as noOfDraws from icc_world_cup
union all
select team_2 as team_name,case when team_2=winner then 1 else 0 end as noOfWins,
case when winner='Draw' then 1 else 0 end as noOfDraws from icc_world_cup
) A
group by 1 order by noOfWins desc

[@Rajkumar-kj1vx]

Thanks for the video ankit;
nitally I was stuck on how to approach this problem, but after seeing adding win_flag column then I have understood it and this is my solution:
Query:
with cte as (select Team_1,case when Team_1 = Winner then 1 else 0 end as win_flag
from icc_world_cup
union all
select Team_2,case when team_2 = Winner then 1 else 0 end as win_flag
from icc_world_cup
)
select Team_1,count(win_flag) as no_of_matches_played,
sum(case when win_flag =1 then 1 else 0 end) as won_matches,
sum(case when win_flag =0 then 1 else 0 end) as lost_matches,
(sum(case when win_flag =1 then 1 else 0 end) *2) as points_scored
from cte
group by Team_1
order by points_scored desc

[@ankitbansal6]

Awesome

[@akashwatar6633]

I am planning to complete all the SQL videos created by you in order to learn SQL. I will post a comment on each video and like it as a checklist for completed videos, starting from the beginning.

[@mayankjha7070]

Great Ankit Bhaiya,
Below query will also work:
-----------------------------------------
with result_set AS
(
select team_1 team, winner, case when team_1= winner then 'win' else 'lose' end result from icc_world_cup
union ALL
select team_2 team ,winner, case when team_2= winner then 'win' else 'lose' end result from icc_world_cup
)
select team, count(team) total_team,
sum(case when result = 'win' then 1 else 0 end) no_of_wins,
sum(case when result = 'lose' then 1 else 0 end) no_of_loss
from result_set
group by team
----------------------------------------
P.S: In love using sum with case when statements, thanks bhaiya, you are my Sandeep bhaiya from TVF UPSC Series.

[@ankitbansal6]

Great 👍

[@akp7-7]

i gone through almost all comment i was searching if it can be done in more short way but i can say you have shared the most efficient solution. Thanks

[@zeeshansalim673]

select team,count(*) as matches,count(win_flag) as win,
count(*)-count(win_flag) as losses
from
(select team_1 as team,case when team_1=winner then 1 end as win_flag
from icc_world_cup
union all
select team_2 as team,case when team_2=winner then 1 end as win_flag
from icc_world_cup) a
group by team;
Thanks for explaining the approach

[@asimmanna803]

Thank you so much Ankit sir for the content. Here is my solution
WITH x AS (
SELECT Team_1 AS team,Winner
FROM icc_world_cup
UNION ALL
SELECT Team_2 AS team,Winner
FROM icc_world_cup)
SELECT team,COUNT(*) AS total_match,
SUM(CASE WHEN team=Winner THEN 1 ELSE 0 END) AS won,
SUM(CASE WHEN team!=Winner THEN 1 ELSE 0 END) AS lost,
ROUND(SUM(CASE WHEN team=Winner THEN 1 ELSE 0 END)*100/COUNT(*),0) AS win_percentage
FROM x
GROUP BY team;

[@jhonsen9842]

I am in love with your content. At first, it was difficult to follow because I was starting from basic, but gradually it became habbit.Hopefully in the future I will be able to crack good company.

[@ankitbansal6]

You can do it!

[@DeepakSharma-rc8ri]

Fantabulous playlist Ankit!! Finally someone broke the shell and putting out lot of hardwork beyond imagination. God bless you brother, keep shining rockstar!!
Now here is my solution including DRAW matches:-
with CTE as (
select Team_1 as team_name, case when Team_1=winner then 1 else 0 end as no_of_wins,
case when winner='Draw' then Team_1 end no_of_draw
from icc_world_cup
union all
select Team_2 as team_name, case when Team_2=winner then 1 else 0 end as no_of_wins,
case when winner='Draw' then Team_2 end no_of_draw
from icc_world_cup
)
select
team_name,
count(team_name) as matches_played,
sum(no_of_wins) as no_of_wins,
count(team_name) - (sum(no_of_wins)+count(no_of_draw)) as no_of_losses,
count(no_of_draw) as no_of_draw
from CTE group by team_name ;

[@ankitbansal6]

Thank you . 🙏

[@hammadahmed1659]

Excellent Sir, Looking forward to learn a lot from your experience ..

[@ankitbansal6]

Keep watching :)

[@jdisunil]

you are a gem and greatest part is you share knowledge.
Here is my attempt:
with mpcte as(
select team_1 as TeamName from icc_world_cup union all
select team_2 as TeamName from icc_world_cup ), mp1 as(
select TeamName,count(1) as MathesPlayed from mpcte group by TeamName ),wins as(
select winner,count(1) as No_of_Wins from icc_world_cup group by winner)
select TeamName,MathesPlayed,isnull(No_of_Wins,0) as No_of_Wins,MathesPlayed-isnull(No_of_Wins,0) as No_of_losses
from mp1 left outer join wins on mp1.TeamName=wins.winner
order by No_of_Wins desc

[@ankitbansal6]

Thanks for posting 👏

[@bhaireviewkarelive5679]

Got this problem in recent Infosys interview, showed interviewer a live demo and cracked it ☺️

[@ankitbansal6]

Awesome 😎

[@BigDataWithSky]

@@ankitbansal6 You're Awesome

[@yatinshekhar787]

2/122
My solution:-
with cte as
(select team_1 as team , case
when team_1 = winner then 1 else 0 end as win from icc_world_cup
union all
select team_2 as team , case
when team_2 = winner then 1 else 0 end as win from icc_world_cup)
select team , count(team) as total_match , sum(win) as wins , count(team) - sum(win) as loss
from cte group by team order by wins desc;

[@nidish.b7005]

Great… really refreshing my plsql knowledge…
We can use decode also instead of using case … both we can get same result…. Thanks…

[@ankitbansal6]

Right decode can also be used..

[@venkateshpaidisetty9578]

Nice one Ankit, my answer is as mentioned below:
Select Team_Name , sum(Matches_played) as Matche_played, NVL(sum(case when Team_Name in (Select winner from icc_world_cup) then 1 end),0) No_of_wins ,
(sum(Matches_played) - NVL(sum(case when Team_Name in (Select winner from icc_world_cup) then 1 end),0)) No_of_losses from
(Select Team_1 as Team_Name, 1 as Matches_Played from icc_world_cup
Union all
Select Team_2 as Team_Name, 1 as Matches_Played from icc_world_cup) T1
group by 1

[@ankitbansal6]

Thanks for posting. Looks good 🙂

[@narutomaverick]

00:03 Discussing the scoring system for ICC tournament
01:13 ICC tournament Points table derivation
02:23 Deriving Points table for ICC tournament
03:31 Discussion on team performance and strategy
04:35 Deriving Points table for ICC tournament
05:46 Creating a points table for ICC tournament
06:46 Derivation of Points table for ICC tournament
07:50 Deriving points table for ICC tournament
My Solution:
--select * from icc_world_cup;
With cte as (
select Team_1 as Team_N, Case when Team_1=Winner then 1 else 0 end as win_flag
from icc_world_cup
UNION ALL
select Team_2 as Team_N, Case when Team_2=Winner then 1 else 0 end as win_flag
from icc_world_cup
)
select Team_N , count(*) as M_Played , sum(win_flag) as M_Won, count(*) - sum(win_flag) as M_Loss
from cte
group by Team_N
order by M_Won desc

[@vikhyatjalota2213]

My solution :
with teams as(
select team_name,count(total_matches_played) as total_matches
from(
select team_1 as team_name,count(team_2) as total_matches_played
from icc_world_cup
group by 1
union all
select team_2 as team_name,count(team_2) as total_matches_played
from icc_world_cup
group by 1) new
group by 1 ),
cte_2 as (
select t.*,count(winner) as total_wins
from teams t left join icc_world_cup c
on t.team_name = c.winner
group by 1,2)
select *, (total_matches - total_wins) as total_loss
from cte_2
Thanks for the question Sir

[@Tanya-og7no]

I am posting my query on this, after watching your video I can see there are some differences in structure

[@ankitbansal6]

Thanks for posting 🙏

[@mohits4841]

My query is a bit lengthy but I think it's easy to understand as well. Thanks Ankit for creating such amazing videos.
with tmp_teams as (
select distinct Team_1 as team_name, count(Team_1) as match_played
from icc_world_cup
group by team_name
union all
select distinct Team_2 as team_name, count(Team_2) as match_played
from icc_world_cup
group by team_name
)
, tmp_played as (
select team_name, sum(match_played) as total_match_played
from tmp_teams
group by team_name
)
, tmp_winner as (
select a.team_name, a.total_match_played, count(b.Winner) as No_of_wins
from tmp_played a
left join icc_world_cup b on a.team_name=b.Winner
group by 1
)
select team_name, total_match_played, No_of_wins, (total_match_played - No_of_wins) as No_of_loss
from tmp_winner
order by No_of_wins ;

[@viswac2302]

bro if you using group by for team_name , then why selecting it as distinct

[@kartika5151]

hello Sir,
I really like your videos. Keep coming with such videos. As anyone can learn concepts but learning how to implement them is important. Thank You🙌

[@praveensinghrathore4542]

OMG, Dude, you made it look so easy, I tried a different approach but you made it so simple, WOW.
Here is my solution though.
WITH TEAM_LIST AS (
SELECT TEAM_1 as TEAMS FROM MATCHES UNION SELECT TEAM_2 as TEAMS FROM MATCHES) ,
CTE AS (SELECT TL.TEAMS, M.* FROM
TEAM_LIST TL
JOIN
MATCHES M ON TL.TEAMS = M.TEAM_1 OR TL.TEAMS = M.TEAM_2)
SELECT DISTINCT(TEAMS) as TEAM_NAME, COUNT(TEAMS) as MATCHES_PLAYED,
COUNT(CASE WHEN WINNER = TEAMS THEN 1 END) AS MATCHES_WON,
COUNT(TEAMS) - COUNT(CASE WHEN WINNER = TEAMS THEN 1 END) as MATCHES_LOST
FROM CTE
GROUP BY TEAMS;

[@ankitbansal6]

Looks good. Thanks for posting 👏

[@vijaypalmanit]

Bro you are amazing, had I found you earlier I could have cleared my Amazon interview.

[@shiroyasha_007]

somewhat complex approach but similar method with partition by:
select team_1,sum(mp) as mp,sum(mw) as mw,sum(ml) as ml,sum(mw)*2 from(
select team_1,count(team_1)over ( partition by team_1) as mp, sum(case when team_1=winner then 1 else 0 end) over ( partition by team_1) as mw,
sum(case when team_1=winner then 0 else 1 end) over ( partition by team_1) as ml from icc_world_cup
union all
select team_2,count(team_2)over ( partition by team_2) as mp, sum(case when team_2=winner then 1 else 0 end) over ( partition by team_2) as mw,
sum(case when team_2=winner then 0 else 1 end) over ( partition by team_2) as ml
from icc_world_cup) abc
group by team_1

[@ankitbansal6]

Thanks for posting 👏

[@RonakSharma-cy1tb]

amazing sir thank you for helping

[@MyVaibhavraj]

I consider a draw match case also calculate total points w.r.t won, lost and draw
select team, count(1) as played_matches, sum(win_flag) as won_matches, count(1)-sum(win_flag) as lost_matches,
sum(draw) as draw, case when sum(draw) = 0 then sum(win_flag)*2 else sum(win_flag)*2+sum(draw)*1 end as total_points
from(
select team_1 as team, case when team_1 = winner then 1 else 0 end as win_flag,
case when winner = 'No Result' then 1 else 0 end as draw from icc_world_cup
union all
select team_2 as team, case when team_2 = winner then 1 else 0 end as win_flag,
case when winner = 'No Result' then 1 else 0 end as draw from icc_world_cup
) A
group by team
order by won_matches desc

[@ankitbansal6]

Calculation for lost matches doesn't consider draw matches in your query.

[@suyash.bihone]

I practiced this question by inserting one row match Draw:-
SELECT
TEAM,
COUNT(1) AS TOTAL_MATCH_PLAYED,
SUM(WIN_FLAG) AS NO_OF_WINS,
COUNT(1)-SUM(WIN_FLAG)-SUM(draw_flag) AS NO_OF_LOSSES,
SUM(DRAW_FLAG)AS DRAW_FLAG
FROM(
SELECT TEAM_1 AS TEAM,CASE WHEN TEAM_1=WINNER THEN 1 ELSE 0 END AS WIN_FLAG,
CASE WHEN UPPER(WINNER) = 'DRAW' THEN 1 ELSE 0 END AS DRAW_FLAG
FROM ICC_WORLD_CUP
UNION ALL
SELECT TEAM_2 AS TEAM,CASE WHEN TEAM_2=WINNER THEN 1 ELSE 0 END AS WIN_FLAG ,
CASE WHEN UPPER(WINNER) = 'DRAW' THEN 1 ELSE 0 END AS DRAW_FLAG
FROM ICC_WORLD_CUP
)
GROUP BY TEAM
ORDER BY NO_OF_WINS DESC;

[@dineshchavan9462]

First of all I'm the big fan of your video's..
I have tried solving the problem..Here is the query
--------------------------------------
with t1 as(
select team_1 as team from icc_world_cup
union all
select team_2 as team from icc_world_cup )
select
a.team,a.total_match,coalesce(b.won,0) as won,a.total_match-coalesce(b.won,0) as loss
from (select team,count(team) as total_match from t1 group by 1) a
left join (select winner,count(winner) as won from icc_world_cup group by 1) b
on a.team = b.winner;
------------------------------------
Please let me know if any correction..
Thank you

[@ankitbansal6]

Awesome !!

[@KoushikT]

Using full outer join
****************
select
coalesce(a.team_1,b.team_2) as Team_name,
count(a.team_1) over (partition by a.team_1) + count(b.team_2) over (partition by b.team_2) as Matches_played,
sum(case when a.team_1 = a.winner then 1 else 0 end) over (partition by a.team_1)
+ sum(case when b.team_2 = b.winner then 1 else 0 end) over (partition by b.team_2)
as no_of_wins,
sum(case when a.team_1 != a.winner then 1 else 0 end) over (partition by a.team_1)
+ sum(case when b.team_2 != b.winner then 1 else 0 end) over (partition by b.team_2)
as no_of_losses
from icc_world_cup a full outer join icc_world_cup b on a.team_1=b.team_2
order by no_of_wins desc

[@ankitbansal6]

Great 😊

[@KisaanTuber]

Thanks again for creating such awesome videos. Here is my solution
with t1 as
(select
team_1,
team_2
from icc_world_cup
UNION ALL
select
team_2 as team_1,
team_1 as team_2
from icc_world_cup),
played as
(select
team_1 as team_name,
count(1) as total_played
from t1
GROUP by 1),
wins as
(select
winner,
count(1) as total_win
from icc_world_cup
GROUP by 1)
select
p.team_name,
p.total_played,
coalesce(w.total_win,0) as total_win,
(p.total_played - coalesce(w.total_win,0)) as total_lost
from played p LEFT join wins w
on p.team_name = w.winner;

[@tamojeetchatterjee9385]

Hi Ankit ,
Thanks for the question.
Here is my approach that provides the exact output
with cte as (select team_1 as team from icc_world_cup
union all
select team_2 from icc_world_cup)
,
gte as (select team , count(team) as matches_played
from cte group by team)
select team , matches_played , matches_win , (matches_played - matches_win) as matches_loss
from (select distinct team , matches_played , count(winner)over(partition by team ) as matches_win
from gte as g
left join
icc_world_cup as c
on g.team = c.winner) as kte order by matches_win desc

[@abb_raj1107]

My alternative sol: for this problem(with addtional draw case):
with cte as(
select * from icc_world_cup t1
union
select team_2,team_1,winner from icc_world_cup t2 )
select distinct(team_1) Team_name ,count(team_2) Matches_play
,sum(case when winner=team_1 then 1 else 0 end) win
,sum(case when winnerteam_1 and winner'Draw' then 1 else 0 end) lose
,sum(case when winner='Draw' then 1 else 0 end) draw
from cte
group by team_1;

[@ankitbansal6]

Thanks for posting 👏

[@adityams1659]

*Im so glad that, I tried solving this question on my own and I was able to solve it!*

[@ankitbansal6]

Awesome

[@suriyas6338]

Hi @Ankit,
My solution to find draw matches :)
select Team_Name, count(Team_Name) as No_of_matches_Played ,
sum(wins) as wins,
count(Team_Name) - sum(wins) - sum(drawFlag) as loss,
sum(drawFlag) as draw from (
select Team_1 as Team_Name, case when Team_1 = Winner then 1
else 0 end as wins,
case when Team_1 != winner and Team_2 != winner then 1
else 0 end as drawFlag from icc_world_cup
union all
select Team_2 as Team_Name,case when Team_2 = Winner then 1
else 0 end as wins,
case when Team_1 != winner and Team_2 != winner then 1
else 0 end as drawFlag from icc_world_cup
) a
group by Team_Name
others can share their thoughts :)

[@prashlovessamosa]

Thank bahiya for free knowledge this playlist helps me a lot thank you

[@ankitbansal6]

Glad to hear that

[@shashankkumar5159]

with temp as (
select team1 as team , winner from cric_match
union all
select team2, winner from cric_match)
select team, count(*) as num_of_match_played,
sum(case when winner = team then 1 else 0 end) as winned_match
from temp
group by team

[@shahakunal1]

Hi @Ankit ,
Here is one more way of implementation
select team_name ,matches_played,coalesce(winn_count,0) as no_of_wins, matches_played - coalesce(winn_count,0) as no_of_losses from (select team_name , count(*) as matches_played from (select distinct team_1 as team_name from icc_world_cup union all select distinct team_2 as team_name from icc_world_cup ) as a group by team_name order by team_name) temp LEFT JOIN (select winner , count(*) as winn_count from icc_world_cup group by winner) fin on temp.team_name = fin.winner order by team_name asc;

[@chhavikkapoor7113]

My solution:
select name,
count(*) as total_visits,
mode(floor) as most_visited_floor,
group_concat(distinct resources) as resources_used
from entries
group by name;

[@MubarakAli-qs9qq]

Best youtube channel for sql

[@gamingwithlegend3183]

I solved this question through cte sir.

[@ankitbansal6]

Cool

[@gamingwithlegend3183]

@@ankitbansal6 thanks sir for your reply I'm working in a company as a database analyst now I'm preparing for data analyst.your videos helping me alot.

[@makarsh29]

good question

[@priyanshisingh9329]

Your videos are great !!!! Please keep posting

[@ankitbansal6]

Thank you. Sure 😊

[@praji3914]

Hi sir your videos are very helpful.I am learning SQL. Please make a video on how to analyse the data to write queries in an easy way. Thanks in advance..

[@rockstarrocky2270]

Thank you.
Here is my solution including the draw case:
SELECT team,COUNT (*) no_of_matches, SUM(CASE WHEN win_flag= '1' Then 1 WHEN win_Flag= '0' Then 0 END) no_of_wins,
SUM(CASE WHEN win_flag= '0' Then 1 ELSE 0 END) as no_of_losses,
SUM(CASE WHEN win_flag= 'D' Then 1 ELSE 0 END) no_of_draws FROM (
SELECT Team_1 as team, CASE WHEN Winner='Draw' Then 'D' WHEN Team_1= Winner THEN '1' ELSE '0' END AS win_flag FROM ICCWORLDCUP
UNION ALL
SELECT Team_2 as team, CASE WHEN Winner='Draw' Then 'D' WHEN Team_2= Winner THEN '1' ELSE '0'
END AS win_flag FROM ICCWORLDCUP) temp GROUP BY team Order BY no_of_wins DESC

[@prashantghalke9084]

Ankit is it possible for you to have more complex problem video comparing Tabeau vs SQL? You have uploaded one video and it was very good learning. Please do it this way we can learn both SQl and Tableau.

[@sanjup100]

Thanks Ankit for the nice tutorial,
This is my solution using CTE
with team_1 as (
select team_1 as team, case when team_1=winner then 1 else 0 end as win_flag from icc_world_cup
union all
select team_2 as team, case when team_2=winner then 1 else 0 end as win_flag from icc_world_cup
)
select team, count(1) as no_of_matches, sum(win_flag) as no_of_wins, count(1)-sum(win_flag) as no_of_losses from team_1 group by team
order by no_of_wins desc;

[@Itskvr]

with team_table as
(select team1 as team from teams
union all
select team2 as team from teams),
table2 as
(select *,count(*) as played from team_table group by team)
select a.team,a.played,count(b.winner) as won, (played-count(b.winner)) as lost from table2 a left join teams b on a.team=b.winner group by a.team,a.played

[@rajn2155]

Different Approach -
with cte as(
select t_ ,count(*) as matches_played from
(
(select team_1 as t_ from icc_world_cup )
union all
(select team_2 as t_ from icc_world_cup )
) as dd
group by 1
)
,cte2 as(
select t_,winner from cte
left join
(select winner from icc_world_cup ) as dd
on dd.winner=cte.t_)
,cte3 as(
select t_,sum(case when winner is not null then 1 else 0 end) as no_of_wins
from cte2 group by 1)
,cte4 as(
select cte.t_,cte.matches_played,cte3.no_of_wins from cte inner join cte3
on cte.t_=cte3.t_)
select *,(matches_played - no_of_wins ) as no_of_losses from cte4

[@sikansamantara9504]

Hi @ankit Below is my try,
with CTE as (
select Team_1 from icc_world_cup
union all
select Team_2 from icc_world_cup
),
cte2 as(
select Team_1,count(*) total_match_palyed from CTE
group by Team_1
),
cte3 as (
select
winner,count(*) as winner_cnt
from icc_world_cup
group by winner
),
cte4 as (
select a.*,coalesce(winner_cnt,0) matches_won from cte2 a left join cte3 b on a.Team_1=b.winner
)
select *,(total_match_palyed-matches_won )as matches_lost from cte4
order by matches_won desc,matches_lost asc

[@SushilChauhan]

i did this using WITH
with maintable AS
(
select team_1 as Team, Winner from icc_world_cup
UNION ALL
select Team_2 as Team, Winner from icc_world_cup
)
select Team,
count(*) as Game_Played,
sum( case when Team= Winner then 1 else 0 end) as Win,
sum( case when Team Winner then 1 else 0 end ) as Lost
FROM maintable
group by Team
order by Team DESC

[@bukunmiadebanjo9684]

Since you said we can make a case for draw, I decided to try that instead. Here is my solution.
with result_table as (select team_1 as team, case when team_1 = winner then 1 else 0 end as win_status,
case when team_1 winner and team_2 winner then 1 else 0 end as draw_status
from icc_world_cupp
union all
select team_2 as team, case when team_2 = winner then 1 else 0 end as win_status,
case when team_1 winner and team_2 winner then 1 else 0 end as draw_status
from icc_world_cupp)
select team, count(team) as matches_played,sum(win_status) as matches_won ,
sum(draw_status) as matches_drawn, count(team) - (sum(win_status) + sum(draw_status)) as matches_lost
from result_table
group by team

[@AbhishekSabharwal131997]

with cte as ( select winner, count(1) as wins from wc group by 1)
, cte2 as ( select team_1 as tm from wc union all select team_2 as tm from wc)
, cte3 as(
select a.tm, count(1) as matches, coalesce(c.wins,0) as wins, count(1)-c.wins as loss
from cte2 a left join cte c on a.tm=c.winner
group by 1
order by c.wins desc)


select * from cte3

[@AnantJain-kg8rv]

Hello Ankit,
I have solve this problem with CTE:
with cte as (
select Team_1 as Team_name, case when team_1 = winner then 1 else 0 end as no_of_winner,
case when team_1 != winner then 1 else 0 end as no_of_loss from icc_world_cup
union all
select Team_2 as Team_name, case when team_2 = winner then 1 else 0 end as no_of_winner,
case when team_2 != winner then 1 else 0 end as no_of_loss from icc_world_cup)
select Team_name, count(Team_name), sum(no_of_winner) as win_matches, sum(no_of_loss) as loss_matches from cte
group by Team_name
order by win_matches desc

[@gazart8557]

Please make more of this type. Thanks a ton

[@ankitbansal6]

Sure 😊

[@kasra7777]

with cte as (
select
team_1 as team, winner
from
icc_world_cup
union all
select
team_2 as team, winner
from
icc_world_cup
)
select
team as Team_Name,
count(team) as Matches_played,
sum(
case when team = winner then 1 else 0 end
) as no_of_wins,
sum(
case when team winner then 1 else 0 end
) as no_of_losses
from
cte
group by
team
order by
team

[@shashwatdev2371]

My solution for this question-
with cte1 as
(
select team_1 as team, count(*) as num1
from icc_world_cup
group by team_1
union all
select team_2 as team, count(*) as num1
from icc_world_cup
group by team_2
),
cte2 as
(
select team, sum(num1) as Matches_Played
from cte1
group by team
),
wins as
(
select winner as team, count(*) as no_of_wins
from icc_world_cup
group by winner
)
select a.team,a.Matches_Played,coalesce(b.no_of_wins,0) as no_of_wins, a.Matches_Played-coalesce(b.no_of_wins,0) as no_of_loss
from cte2 as a
left join wins as b on a.team=b.team
order by no_of_wins desc;

[@viswa6126]

Thanks for the explanation.

[@chirag__vlogsinfo80]

with cte as
(select team_1 as firstteam, team_2 as secondteam, Winner from Winners
union
select team_2 as firstteam, team_1 as secondteam , Winner from Winners),
cte2 as
(select firstteam,count(*) as matchesplayed, sum(case when winner=firstteam then 1 else 0 end) as matcheswin from cte
group by firstteam)
select * , (matchesplayed - matcheswin) as numberofloss from cte2

[@Ankit-rv2my]

with cte as (select team_1 as team ,winner from icc_world_cup
union all
select team_2,winner as team from icc_world_cup)
select team, count(1) as matches_played,
sum(case when team=winner then 1 else 0 end) as win_flag,
sum(case when team!=winner then 1 else 0 end )as loss_flag
from cte
group by team

[@exanode]

select a.teams,
count(a.teams) No_OF_MATCHES,
b.no_of_wins,
(count(a.teams) - b.no_of_wins) No_of_losses
from(
select team_1 Teams from icc_world_cup
union all
select team_2 Teams from icc_world_cup
) a
join
(
select winner, count(winner) no_of_wins
from icc_world_cup
group by winner
) b
on a.teams = b.winner
group by teams,b.no_of_wins

[@hiralalpatra500]

with cte as (
select team_1,winner from icc_world_cup
union all
select team_2,winner from icc_world_cup)
select team_1 as team_name,
count(team_1) as matches_played,
sum(case when team_1=winner then 1 else 0 end) as no_of_wins,
sum(case when team_1winner then 1 else 0 end) as no_of_losses from cte
group by team_1

[@badamsunilkumar]

Hi @Ankit
I tried without seeing your results but it came little lengthly query. But it is very interesting question
select icc.team,
case when final.no_played is not null then final.no_played
else 0 end no_played
,
case when final.no_win is not null then final.no_win
else 0 end no_win
,
(case when final.no_played is not null then final.no_played
else 0 end -
case when final.no_win is not null then final.no_win
else 0 end) no_loss
from (select team_1 as team from icc_world_cup
union
select team_2 as team from icc_world_cup) icc left outer join
(select t1.team,t1.no_played,t2.no_win,(t1.no_played-t2.no_win) as no_loss from
(select team,count(team) as no_played from(
select team_1 as team from icc_world_cup
union all
select team_2 as team from icc_world_cup
)
group by team) t1
,
(select winner as team,count(*) as no_win from icc_world_cup
group by winner) t2
where t1.team=t2.team(+)) final
on icc.team=final.team;

[@guptaashok121]

Easiest solution i could think, without refering others:
with winners AS
(select winner as team,count(1) as wins from icc_world_cup GROUP by winner)
select a.team,
sum(played) as no_of_match_played,
sum(a.wins) as no_of_wins,
sum(played)-a.wins as no_of_loss
FROM
(
select team_1 as team,count(1) as played from icc_world_cup group by team_1
union ALL
select team_2 as team,count(1) as played from icc_world_cup group by team_2
) played
join winners a on a.team=played.team
group by a.team

[@ankitbansal6]

Approach looks good. on a.wins i think you need to use some aggregation

[@guptaashok121]

@@ankitbansal6 its already aggregated in CTE as its count.

[@ankitbansal6]

@@guptaashok121 then take that column in last group by also otherwise you will get syntax error.

[@guptaashok121]

@@ankitbansal6 you are right I should be getting error but surprisingly its not giving any error, tested in sqlliteonline. its giving right result.

[@ankitbansal6]

@@guptaashok121 sqllite and MySQL i think support this but ideally it won't work in other databases

[@vinaykumar-hb7rf]

Hi Ankit,
Please check below solution if it looks good.
with win_match as (
select winner as team_name,count(1) as no_of_win from icc_world_cup group by winner),
match_played as (
select team_name,sum(match_played) as no_of_match_played from(
(select Team_1 as team_name,count(1) as match_played from icc_world_cup group by Team_1)
union all
(select Team_2 as team_name,count(2) as match_played from icc_world_cup group by Team_2)
) as abc
group by team_name)
select a.team_name as team_name,a.no_of_match_played,ISNULL(b.no_of_win,0) as no_of_win_match, (a.no_of_match_played- (ISNULL(no_of_win,0))) AS no_0f_losses
from match_played a LEFT OUTER join win_match b on a.team_name=b.team_name;

[@ankitbansal6]

This looks good. Thanks for posting 👏

[@pavitrashailaja850]

Very nicely explained ✌️

[@ankitbansal6]

Thank you

[@thegamingtron8698]

I was able to solve the question using multiple CTE's. Might not be an optimized way to solve this. I will go through the video now to learn the best way to solve this
with cte_1 as (Select Team_1 as Team from icc_world_cup
union all
Select Team_2 from icc_world_cup),
cte_2 as (Select Team, count(Team) as no_of_matches_played from cte_1
group by Team),
cte_3 as (Select winner, count(winner) as no_of_wins from icc_world_cup
group by winner),
cte_4 as (Select Team,no_of_matches_played,ISNULL(no_of_wins, 0 ) as no_of_wins
from cte_2 left join cte_3 on Team = winner)
Select *, ABS(no_of_wins-no_of_matches_played) as no_of_losses from cte_4

[@ankitbansal6]

Good attempt 👍

[@yetasudhakar]

Hi Ankit , I tried this way
;with cte as
(select Team_1 team_name from icc_world_cup
UNION
SELECT Team_2 FROM icc_world_cup A)
,cte2 as
(select team_name,count(a.Team_1)+count(b.Team_2) Matches_played from cte
left join icc_world_cup a on cte.team_name=a.Team_1
left join icc_world_cup b on cte.team_name=b.Team_2
group by cte.team_name)
select cte2.*,count(Winner) Matchews_win,Matches_played-count(Winner) matches_loss from cte2
left join icc_world_cup a on a.Winner=cte2.team_name
group by Matches_played,team_name

[@ankitbansal6]

Have you tried running this query ?

[@pedro-ik1mr]

here is my solution :
with cte as
(select team_1,winner
from icc_world_cup
union all
select team_2,winner
from icc_world_cup)
select Team_1,count(team_1) as No_of_match
,sum(case when team_1 = winner then 1 else 0 end) no_of_wins
,sum(case when team_1 winner then 1 else 0 end) no_of_lose
from cte
group by Team_1

[@abhimistry9226]

thanks bansal sir , great explanation ;)

[@biswajitpradhan6121]

Got to learn some new concepts & new approaches. Hopefully will build a good career in data analysis filed . My approach to get the output (result includes draws) (team, matchesplayed, wins, draws, losses )
with table1 as
(
SELECT * FROM Table_4
union
select Team_2 as T1 , Team_1 as T2 , Winner as W2 from Table_4
) ,
table2 as
(
select Team_Name , matches_played , sum(RN) as wins , draws = 0
from (
select Team_1 as Team_Name , count(1) over(partition by Team_1) as matches_played , case when Team_1 = Winner then 1 else 0 end as RN
from table1
WHERE Winner 'DRAW'
) x
group by x.Team_Name , x.matches_played
) ,
table3 as
(
select Team_1 as Team_Name , count(1) over(partition by Team_1) as matches_played , Wins = 0 , count(1) over(partition by Team_1) as draws
from table1
WHERE Winner = 'DRAW'
) ,
table4 as
(
select Team_Name , sum(matches_played) as matches_played , sum(wins) as wins , sum(draws) as draws
from (
select * from table2
union
select * from table3
) x
group by x.Team_Name
)
select Team_Name , matches_played , wins , draws , losses = matches_played - (wins + draws)
from table4
order by wins desc

[@ankitbansal6]

Great

[@prasaddalvi5230]

with cte as
(
select team_1 as team,Winner from icc_world_cup
union all
select team_2 as team,Winner from icc_world_cup
)
select
team,
count(team) as no_of_matches,
sum(case when team=Winner then 1 else 0 end) as no_of_win,
count(team)-sum(case when team=Winner then 1 else 0 end) as no_of_losses
from cte
group by 1

[@poisontech4075]

with CTE as (select Team_1, case when team_1=winner then 1 else 0 end as won
from icc_world_cup
union all
select Team_2, case when team_2=winner then 1 else 0 end as won
from icc_world_cup)
select team_1 as team, count(won) as matches_played, sum(won) as win, count(won)-sum(won) as losses from CTE
group by team;

[@abb_raj1107]

Ans for the additional draw case:
select team_1
,count(team_1)
,sum(flag) win
,count(flag) - sum(flag) lose
,count(*) - count(flag)
from(
(select team_1,winner
,case when team_1 = winner then 1 when winner ='Draw' then null else 0 end flag
from icc_world_cup )
union all
(select team_2,winner
,case when team_2 = winner then 1 when winner ='Draw' then null else 0 end flag
from icc_world_cup)
order by team_1
) query1
group by team_1

[@ankitbansal6]

Thank you 😊

[@mohinithor3297]

Hi, your videos are interesting, and easy to understand, can you please make some videos on performance tuning and query optimization also. TIA

[@ankitbansal6]

Sure

[@JAVA_TECH160]

@@ankitbansal6 make sure mysql project in rela time how we will do

[@diptaganguly2346]

Very nice!

[@9844468928]

Hi Ankit sir, as you told i have added the one more column for the number of matches draw SELECT
team_name,
SUM(winning_flag) AS Total_number_win,
COUNT(*) AS total_number_played_match,
COUNT(*) - SUM(winning_flag) AS total_number_loses,
SUM(CASE WHEN winner = 'Draw' THEN 1 ELSE 0 END) AS Number_Draw_matches
FROM
(
SELECT Team_1 AS team_name,
CASE WHEN Team_1 = Winner THEN 1 ELSE 0 END AS winning_flag,
Winner
FROM icc_world_cup
UNION ALL
SELECT Team_2 AS team_name,
CASE WHEN Team_2 = Winner THEN 1 ELSE 0 END AS winning_flag,
Winner
FROM icc_world_cup
) AS k
GROUP BY team_name
ORDER BY SUM(winning_flag) DESC; thank you so much for this video this is helping me a lot

[@radhikagupta7314]

with cte as (
select team_1 as team,
case when team_1 = winner then 1 else 0 end as win_flag
from icc_world_cup
union all
select team_2 as team,
case when team_2 = winner then 1 else 0 end as win_flag
from icc_world_cup )
select distinct team, count(1) as matches_played, sum(win_flag) as matches_won,
count(1) - sum(win_flag) as matches_lost
from cte
group by team

[@user-gm3qx3cf9g]

Hello @ankitbansal6, Request you to plz give the solution to the scenario (DRAW) which you discussed at the end of the video. Thanks.

[@vijayakumarj8124]

Select team,
count(team) as matches_played,
Sum(case when team = winner then 1 else 0 end) as matches_won,
(matches_played - matches_won) as matches_lost
FROM
(
SELECT team1 as team, winner
FROM icc_matches
UNION ALL
SELECT team2 as team, winner
FROM icc_matches
) sub
GROUP BY 1

[@ankitbansal6]

Good one. Thanks for posting.

[@lovecmasam]

Hi Ankit, I tried in another way just wanted to know how to get rid off NULL in column win and loss below is my query:
with ct1 as (
select Team_1 as Team from icc_world_cup
union all
select Team_2 as Team from icc_world_cup
),
ct2 as (
select Winner, count(Winner) as No_of_Win from icc_world_cup
group by Winner),
ct3 as(
select Team, count(team) as No_of_Match from ct1
group by Team)
select a.*, b.No_of_Win,
a.No_of_Match - b.No_of_win as No_of_Loss
from ct3 as a left join ct2 as b
on a.Team=b.Winner

[@rohitbobson]

Thanks @Ankitbansal6 for posting, Here's my take in it.
-- adding draw scenario
INSERT INTO icc_world_cup values('Aus','India',NULL);
WITH CTE AS
(
select Team_1 as TEAM_NAME ,Winner from icc_world_cup
UNION ALL
select Team_2 AS TEAM_NAME ,Winner from icc_world_cup
)
SELECT TEAM_NAME
,COUNT(TEAM_NAME) No_of_matches_playes
,SUM(CASE WHEN TEAM_NAME = Winner THEN 1 ELSe 0 END) No_of_Win
,SUM(CASE WHEN Winner IS NULL THEN 1 ELSe 0 END) No_of_Draw
FROM CTE
Group by TEAM_NAME