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.
@adityabaha2 жыл бұрын
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 ❤
@ankitbansal62 жыл бұрын
Time to celebrate,🎉🎉 Many congratulations 👏
@rishav1442 жыл бұрын
@@ankitbansal6 awesome videos 🔥
@kunalr_ai2 жыл бұрын
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
@rishav1442 жыл бұрын
@@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_ai2 жыл бұрын
@@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.
@shafiq1472 ай бұрын
But during interview interviewer not allowed to use chatgpt
@BismarckWangkhem2 жыл бұрын
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-ty4ru2 жыл бұрын
Have not found any other channel with such practical relatable, unique examples
@anthonychilaka11 ай бұрын
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
@youcanyouwill20043 ай бұрын
@@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..
@venkateshraja39947 ай бұрын
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-qs9qq2 ай бұрын
Did u get placed somewhere
@mohitmotwani92562 ай бұрын
@@MubarakAli-qs9qq I was actually able to convert 5 offers including 2 product based companies
@mohitmotwani92562 ай бұрын
@@MubarakAli-qs9qq I was actually able to convert 5 offers including 2 Product based companies
@mohitmotwani92562 ай бұрын
@@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
@makarsh292 ай бұрын
just replaced subquery with cte
@SudhirKumar-rl4wt2 жыл бұрын
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
@ankitbansal62 жыл бұрын
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-bh4bb11 күн бұрын
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
@milindzuge9062 жыл бұрын
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.
@வசந்த்குமார்தியாகராஜன்8 ай бұрын
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
@ankitbansal68 ай бұрын
Great job
@shatirdimag1011Ай бұрын
best channel for leanring complex sql thanks Ankit Sir
@akp7-72 жыл бұрын
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 👍
@rakeshchaudhary30552 жыл бұрын
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
@jhonsen98427 ай бұрын
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.
@ankitbansal67 ай бұрын
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
@narutomaverick2 ай бұрын
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;
@KoushikT2 жыл бұрын
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
@ankitbansal62 жыл бұрын
Great 😊
@shiroyasha_0072 жыл бұрын
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
@ankitbansal62 жыл бұрын
Thanks for posting 👏
@BhanuGupta-x2j2 ай бұрын
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;
@swethathiruppathy99735 ай бұрын
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-rc8ri2 жыл бұрын
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 ;
@ankitbansal62 жыл бұрын
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
@jdisunil2 жыл бұрын
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
@ankitbansal62 жыл бұрын
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
@hammadahmed16592 жыл бұрын
Excellent Sir, Looking forward to learn a lot from your experience ..
@ankitbansal62 жыл бұрын
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;
@tamojeetchatterjee93856 ай бұрын
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 :)
@venkateshpaidisetty95782 жыл бұрын
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
@ankitbansal62 жыл бұрын
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🙌
@vikhyatjalota22133 ай бұрын
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-og7no2 жыл бұрын
I am posting my query on this, after watching your video I can see there are some differences in structure
@ankitbansal62 жыл бұрын
Thanks for posting 🙏
@nidish.b70052 жыл бұрын
Great… really refreshing my plsql knowledge… We can use decode also instead of using case … both we can get same result…. Thanks…
@ankitbansal62 жыл бұрын
Right decode can also be used..
@vijaypalmanit2 жыл бұрын
Bro you are amazing, had I found you earlier I could have cleared my Amazon interview.
@dineshchavan94622 жыл бұрын
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
@ankitbansal62 жыл бұрын
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;
@adityams16592 жыл бұрын
*Im so glad that, I tried solving this question on my own and I was able to solve it!*
@ankitbansal62 жыл бұрын
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;
@prashantghalke90845 ай бұрын
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.bihone5 ай бұрын
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_raj11072 жыл бұрын
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;
@ankitbansal62 жыл бұрын
Thanks for posting 👏
@98444689283 ай бұрын
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-cy1tb2 ай бұрын
amazing sir thank you for helping
@shahakunal16 ай бұрын
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-hb7rf2 жыл бұрын
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;
@ankitbansal62 жыл бұрын
This looks good. Thanks for posting 👏
@AnantJain-kg8rv10 ай бұрын
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
@priyanshisingh93292 жыл бұрын
Your videos are great !!!! Please keep posting
@ankitbansal62 жыл бұрын
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.
@biswajitpradhan61212 жыл бұрын
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
@ankitbansal62 жыл бұрын
Great
@praji39142 жыл бұрын
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..
@lovecmasam2 жыл бұрын
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
@rockstarrocky22702 жыл бұрын
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
@Itskvr2 жыл бұрын
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_raj11072 жыл бұрын
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
@ankitbansal62 жыл бұрын
Thank you 😊
@praveensinghrathore45422 жыл бұрын
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;
@ankitbansal62 жыл бұрын
Looks good. Thanks for posting 👏
@DeepakSharma-pn8yt4 ай бұрын
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
@rajveerdhumal314315 күн бұрын
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-d3i5 ай бұрын
Hello @ankitbansal6, Request you to plz give the solution to the scenario (DRAW) which you discussed at the end of the video. Thanks.
@amitsaurabh99482 жыл бұрын
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
@devashishnegi46292 жыл бұрын
very well explained. enjoyed it. I have one doubt. Why did you use count(1) and could we use count(*) here? Thanks!
@ankitbansal62 жыл бұрын
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.
@devashishnegi46292 жыл бұрын
@@ankitbansal6 thanks for the clarification!
@krishnapavanayitha57492 жыл бұрын
@@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
@phanidharswarna2 жыл бұрын
@@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
@Ramesh003002 жыл бұрын
@@krishnapavanayitha5749 Very well explained. Can we say it as query performance improvement?
@sikansamantara95043 ай бұрын
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-qs9qq2 ай бұрын
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
@viswa61264 ай бұрын
Thanks for the explanation.
@mohinithor32972 жыл бұрын
Hi, your videos are interesting, and easy to understand, can you please make some videos on performance tuning and query optimization also. TIA
@ankitbansal62 жыл бұрын
Sure
@fruite_145 Жыл бұрын
@@ankitbansal6 make sure mysql project in rela time how we will do
@adityareal322 жыл бұрын
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
@ankitbansal62 жыл бұрын
Just a sub query alias . Mandatory in SQL server
@gazart8557 Жыл бұрын
Please make more of this type. Thanks a ton
@ankitbansal6 Жыл бұрын
Sure 😊
@kashmirshadows81502 жыл бұрын
Under Count (1) , what is 1 ? Where did you get this? Pleas answer.
@ankitbansal62 жыл бұрын
Count(1) is just counting a constant value 1 for each row . Count(1), count(*), count (99) ..all are same
@srishtidixitsd6 ай бұрын
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_Jose2 ай бұрын
Hi Ankit, I have started studying your playlist . Can you tell me about the join logic
@ankitbansal62 ай бұрын
kzbin.info/www/bejne/nIebeox-hNGChMk
@pavitrashailaja8502 жыл бұрын
Very nicely explained ✌️
@ankitbansal62 жыл бұрын
Thank you
@abhimistry92262 жыл бұрын
thanks bansal sir , great explanation ;)
@SushilChauhan3 ай бұрын
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
@ashpoghosh76452 жыл бұрын
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
@ankitbansal62 жыл бұрын
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-rv2my7 ай бұрын
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
@chhavikkapoor71133 ай бұрын
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;
@satyaprakashtadisetti39552 жыл бұрын
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
@ranjeetj307810 ай бұрын
@@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 ;
@ranjeetj307810 ай бұрын
@@prateekpatel1545 yours loses wont give proper value