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

[@MohanGonnabathula]

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

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

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

[@sanjanaaa17]

Then it is highly doubtful that you have been in industry for 5 years

[@Howto-ty4ru]

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

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

[@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..

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

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

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

[@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 😊

[@Jitan_6]

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

[@saikumar_andhavarapu]

​@@Jitan_6 because he unioned both columns and make a single column named team1.

[@Jitan_6]

@@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??

[@surajnadagouda-bh4bb]

Great video and very clear explanation, here is the solution for the same using joins with cte as (select teams,count(1) as no_of_matches from (select team_1 as teams from icc_world_cup union all select team_2 as teams from icc_world_cup) a group by teams) select teams,no_of_matches,count(b.winner) as total_matches_won,no_of_matches-count(b.winner) as total_matches_lost from cte a join icc_world_cup b on a.teams=b.winner group by teams,no_of_matches order by total_matches_won

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

[@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.

[@வசந்த்குமார்தியாகராஜன்]

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

[@shatirdimag1011]

best channel for leanring complex sql thanks Ankit Sir

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

[@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 👍

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

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

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

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

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

[@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!

[@sabarishk1849]

Just what i wanted , amazing question and with simple explanation , good one mate

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

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

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

[@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 😊

[@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 👏

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

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

[@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 . 🙏

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

[@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 👏

[@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.

[@bhaireviewkarelive5679]

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

[@ankitbansal6]

Awesome 😎

[@BigDataWithSky]

@@ankitbansal6 You're Awesome

[@hammadahmed1659]

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

[@ankitbansal6]

Keep watching :)

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

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

[@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 :)

[@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 🙂

[@Kirankumar-ml1ro]

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(1) as matches_played, sum(case when winner=team then 1 else 0 end) as no_of_winners, sum(case when winnerteam then 1 else 0 end) as no_of_losses from cte group by team

[@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🙌

[@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 🙏

[@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..

[@vijaypalmanit]

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

[@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 !!

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

[@adityams1659]

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

[@ankitbansal6]

Awesome

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

[@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.

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

[@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 👏

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

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

[@RonakSharma-cy1tb]

amazing sir thank you for helping

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

[@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 👏

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

[@prashlovessamosa]

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

[@ankitbansal6]

Glad to hear that

[@priyanshisingh9329]

Your videos are great !!!! Please keep posting

[@ankitbansal6]

Thank you. Sure 😊

[@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.

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

[@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..

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

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

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

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

[@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 😊

[@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 👏

[@DeepakSharma-pn8yt]

Hi Ankit, I am trying with below query but in some cases my subtraction is not possible. Am unable to debug why this happing. WITH CTE_1 as ( select Team_1 as team_name from icc_world_cup UNION ALL select Team_2 as team_name from icc_world_cup), CTE_2 as( Select winner, count(*) as winning_matches_count FROm icc_world_cup group by winner), CTE_3 as( select team_name,count(*) as matches_played from CTE_1 GROUP BY team_name) select a.team_name,a.matches_played,---b.winning_matches_count,a.matches_played-b.winning_matches_count CASE when b.winning_matches_count is null then 0 else b.winning_matches_count end as winning_matches_count, case when a.matches_played-b.winning_matches_count is null then 0 else a.matches_played-b.winning_matches_count end as loss_matches from CTE_3 as a LEFT JOIN CTE_2 as b ON a.team_name = b.winner

[@rajveerdhumal3143]

great work thanks ankit

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

[@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 👍

[@AbhinavGupta-d3i]

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

[@amitsaurabh9948]

hello sir, great video as always but I cant do it on my own its too complex for me. what should I do to improve

[@devashishnegi4629]

very well explained. enjoyed it. I have one doubt. Why did you use count(1) and could we use count(*) here? Thanks!

[@ankitbansal6]

Both works. Count(1) , count (2) , count (100) all are same. It is just counting a constant number as times as no of rows in table.

[@devashishnegi4629]

@@ankitbansal6 thanks for the clarification!

[@krishnapavanayitha5749]

​@@devashishnegi4629 To improve execution we use count(1) instead of count(*). Because when we use count(*) it actually counts the records but when we use count(1) its just counts constants for every record so execution time will improve. You many not observe the difference with small datasets but with big amount of data you will find the difference

[@phanidharswarna]

@@krishnapavanayitha5749 That's not true, as per my knowledge count(*) or count(1) or count(100) anything has two phases 1) create a new column with the value you provided i.e. * or 1 or 100 2) count the number of *'s or 1's in the created column @Ankit Bansal correct me if i am wrong

[@Ramesh00300]

@@krishnapavanayitha5749 Very well explained. Can we say it as query performance improvement?

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

[@MubarakAli-qs9qq]

Best youtube channel for sql

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

[@viswa6126]

Thanks for the explanation.

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

[@fruite_145]

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

[@adityareal32]

just confused about one thing. why is there an 'a' after the bracket in the code below as win_flag from icc_world_cup) a

[@ankitbansal6]

Just a sub query alias . Mandatory in SQL server

[@gazart8557]

Please make more of this type. Thanks a ton

[@ankitbansal6]

Sure 😊

[@kashmirshadows8150]

Under Count (1) , what is 1 ? Where did you get this? Pleas answer.

[@ankitbansal6]

Count(1) is just counting a constant value 1 for each row . Count(1), count(*), count (99) ..all are same

[@srishtidixitsd]

Here's the query for Draw Match Scenario: SELECT team_name, COUNT(*) AS total_matches_played, SUM(CASE WHEN win_flag=1 THEN 1 ELSE 0 END) AS no_of_matches_won, COUNT(*)-SUM(CASE WHEN win_flag=1 OR win_flag='DRAW' THEN 1 ELSE 0 END) AS no_of_matches_lost, SUM(CASE WHEN win_flag='DRAW' THEN 1 ELSE 0 END) AS no_of_draw_matches FROM (SELECT team_1 AS team_name, CASE WHEN team_1=winner THEN 1 WHEN team_1!= winner AND winner='DRAW'THEN 'DRAW' ELSE 0 END AS win_flag FROM icc_world_cup UNION ALL SELECT team_2 AS team_name, CASE WHEN team_2=winner THEN 1 WHEN team_2 != winner AND winner='DRAW'THEN 'DRAW' ELSE 0 END as win_flag from icc_world_cup) A GROUP BY team_name ORDER BY no_of_matches_won desc, no_of_matches_lost;

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

[@Abhilash_Jose]

Hi Ankit, I have started studying your playlist . Can you tell me about the join logic

[@ankitbansal6]

kzbin.info/www/bejne/nIebeox-hNGChMk

[@pavitrashailaja850]

Very nicely explained ✌️

[@ankitbansal6]

Thank you

[@abhimistry9226]

thanks bansal sir , great explanation ;)

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

[@ashpoghosh7645]

Including the Draw Case as well.. select temp.team_1 as Team , sum(ifnull((case when temp.win_flag=1 then 1 end ),0))as Wins, sum(ifnull((case when temp.win_flag='D' then 1 end ),0))as Draws, count(temp.team_1)- (sum(ifnull((case when temp.win_flag=1 then 1 end ),0)) + sum(ifnull((case when temp.win_flag='D'then 1 end ),0))) as Loses, count(temp.team_1) as Total_Matches from( select team_1, case when winner=team_1 then 1 when winner ="Draw" then 'D' else 0 end as win_flag from icc_world_cup union all select team_2, case when team_2=winner then 1 when winner ="Draw" then 'D' else 0 end as win_flag from icc_world_cup )temp group by temp.team_1

[@ankitbansal6]

Great. Thanks for posting 👏

[@yogeshwarmishra2730]

Just started waching your vedios ,this solution qry could be best for mentioned problem statement but if fourth insert value would have (Eng,NZ,Eng) then result wouldn't be correct, correct me if i am wrong.

[@raghavendranramachari488]

Hi Ankit, Can you please suggest a sandbox where i could run my SQL statements (create table, insert and then create the SELECT statement)

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

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

[@satyaprakashtadisetti3955]

FOR DRAW CASE: (Is there anything we can do to make it more easy?) select team_name,count(1) as matches,SUM(flag) as wins,count(1)-SUM(flag)-SUM(draw) as loses,SUM(draw) as draw from (select *,case when flag='D' then 1 else 0 end as draw from (select Team_1 as team_name, case when Winner=Team_1 then 1 when Winner='Draw' then 'D' else 0 end as flag from icc_world_cup union all select Team_2 as team_name, case when Winner=Team_2 then 1 when Winner='Draw' then 'D' else 0 end as flag from icc_world_cup )) group by team_name order by wins desc

[@prateekpatel1545]

select team,count(team) as no_of_matches_played,sum(win_flag) as no_of_wins,count(team)-sum(win_flag) as no_of_losses,sum(draw_flag) as no_of_draws from (select team_1 as team,case when team_1 = winner then 1 else 0 end as win_flag,case when 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 winner = 'Draw' then 1 else 0 end as draw_flag from icc_world_cup)a group by team order by no_of_wins DESC

[@ranjeetj3078]

@@prateekpatel1545 select tm_name, count(1) as no_matches_played, sum(win_flg) as no_of_wins , count(1)-(sum(draw_flag)+sum(win_flg)) as no_of_loss ,sum(draw_flag) as no_of_tie from( select t1 as tm_name, case when t1=winner then 1 else 0 end as win_flg ,case when winner = 'Draw' then 1 else 0 end as draw_flag from icc_wc union all select t2 as tm_name, case when t2=winner then 1 else 0 end as win_flg ,case when winner = 'Draw' then 1 else 0 end as draw_flag from icc_wc) A group by tm_name order by no_of_wins desc ;

[@ranjeetj3078]

@@prateekpatel1545 yours loses wont give proper value