ICC Cricket World Cup 2023 Points Table using SQL | Aam vs Mentos Life
Вставка
- Опубліковано 13 жов 2023
- In this video we will derive ICC CWC 2023 points table using SQL. We will solve this problem using 2 approaches . Here is the script:
create table icc_world_cup
(
match_no int,
team_1 Varchar(20),
team_2 Varchar(20),
winner Varchar(20)
);
INSERT INTO icc_world_cup values(1,'ENG','NZ','NZ');
INSERT INTO icc_world_cup values(2,'PAK','NED','PAK');
INSERT INTO icc_world_cup values(3,'AFG','BAN','BAN');
INSERT INTO icc_world_cup values(4,'SA','SL','SA');
INSERT INTO icc_world_cup values(5,'AUS','IND','IND');
INSERT INTO icc_world_cup values(6,'NZ','NED','NZ');
INSERT INTO icc_world_cup values(7,'ENG','BAN','ENG');
INSERT INTO icc_world_cup values(8,'SL','PAK','PAK');
INSERT INTO icc_world_cup values(9,'AFG','IND','IND');
INSERT INTO icc_world_cup values(10,'SA','AUS','SA');
INSERT INTO icc_world_cup values(11,'BAN','NZ','NZ');
INSERT INTO icc_world_cup values(12,'PAK','IND','IND');
INSERT INTO icc_world_cup values(12,'SA','IND','DRAW');
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #cwc2023 #analytics #cricketanalysis
Hit the like button for Rohit's captaincy and batting 🇮🇳🇮🇳🔥🔥
Nice video sir
nice sir love ur content
Thanks a lot🙏
Thanks for knowledge sharing bhai 🙂
Keep watching
Hi Ankit ,
Your SQL video are really nice and helpful but this question is your 1st video in other playlist . And you have covered all SQL questions mostly it is repeating sometimes..
Can you put more videos on Python or AWS services it will be helpful for DE roadmap.
Hi Ankit , Thanks for sharing questions. I tried myself and my sol :-
select TEAM, count(TEAM) T,COUNT(W) AS W,COUNT(L) AS L,COUNT(D) AS D,COUNT(W)*2+COUNT(D) AS P
from (
SELECT TEAM_1 AS TEAM,
CASE WHEN WINNER=TEAM_1 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W ,
CASE WHEN WINNER=TEAM_1 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L ,
CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D
FROM icc_world_cup
UNION ALL
SELECT TEAM_2 AS TEAM,
CASE WHEN WINNER=TEAM_2 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W,
CASE WHEN WINNER=TEAM_2 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L ,
CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D
FROM icc_world_cup ) AS WC
group by TEAM;
Good attempt. Can be simplified 👍
Great video as usual sir!!
Mentos zindagi 💯
Maybe we can create a draw flag similar to a win flag and then add it to pts
Try it out
Hi Ankit I got an mentos approach 😁😁 for this SQL PROBLEM :
with cte as (select team_1 ,winner from icc_world_cup
union all
select team_2 , winner from icc_world_cup),CTE1 AS (
select team_1,count(team_1)Total,sum(case when team_1 = winner then 1 else 0 end)won ,sum(case when team_1 != winner then 1 else 0 end) lost,
sum(case when winner = 'DRAW' then 1 else 0 end) Draw from cte
group by team_1)
select *, (won *2)+Draw points from CTE1
Nice video Ankit… im gonna try same concept using Python instead of sql… I was thinking maybe update the points table everyday using automated web scraping… not sure if it’s possible just a thought
You can just recalculate full table every day
Hi Ankit my answer with DRAW :
with cte as (select match_no,team_1,winner from icc_world_cup
union all
select match_no,team_2,winner from icc_world_cup)
select cte.team_1,count(team_1) as matches_played, sum(case when team_1 = winner then 2 else 0 end) as pts, count(case when team_1 = winner then 2 end) as matches_won,
count(team_1)-count(case when team_1 = winner or winner = 'draw' then 2 end) as losses from cte group by team_1 order by pts desc
with match as
(
SELECT team_1 as team,winner from icc_world_cup iwc
union all
SELECT team_2 as team,winner from icc_world_cup iwc
)
select team,count(team) Matchs_played,
sum(case when team = winner then 1 else 0 end ) Win,
sum(case when winner is null then 1 else 0 end ) Draw,
sum(case when team != winner then 1 else 0 end ) lost,
sum(case when team = winner then 1 else 0 end) * 2 Points
from match
group by team
select
team,
Matchs_played,
win,
lost,
Draw,
sum(win*2+Draw*1) as pnts
FROM
(with matches_all as
(
SELECT team_1 as team,winner from icc_world_cup iwc
union all
SELECT team_2 as team,winner from icc_world_cup iwc
)
select team,count(team) Matchs_played,
sum(case when team = winner then 1 else 0 end ) Win,
sum(case when winner = 'DRAW' then 1 else 0 end ) Draw,
sum(case when team != winner and team != 'DRAW'then 1 else 0 end ) lost
from matches_all
group by team) B
GROUP BY team
order by Matchs_played desc;
this would be correct Answer bhai
what is the problem statement ?
Hi Ankit Sir, Thanks for sharing the questions. You don't need to create separate group by in each union as per my understanding, Please correct me If i am wrong. Below is my solution ;)
select w.team_1 as Teams, count(*) P, sum(w.t1_won) W, (count(*) - sum(w.t1_won)) L, sum(w.t1_won)*2 Pts, sum(w.draw) ties from
(select team_1, (case when team_1 = winner then 1 else 0 end) t1_won, (case when winner = 'Draw' then 1 else 0 end) as draw
from icc_world_cup
union all
select team_2, (case when team_2 = winner then 1 else 0 end) t2_won, (case when winner = 'Draw' then 1 else 0 end) as draw
from icc_world_cup)w
group by w.team_1
order by Pts desc
You are right 👍
Hi, Your query considers the draw as loss match.
Hey Ankit Thanks for the question here is my attempt
WITH table1
AS (SELECT team_1 AS team_name,
CASE
WHEN team_1 = winner THEN 1
ELSE 0
END AS Win,
CASE
WHEN winner LIKE '%draw%' THEN 1
ELSE 0
END AS draw
FROM icc_world_cup_2023
UNION ALL
SELECT team_2 AS team_name,
CASE
WHEN team_2 = winner THEN 1
ELSE 0
END AS Win,
CASE
WHEN winner LIKE '%draw%' THEN 1
ELSE 0
END AS draw
FROM icc_world_cup_2023)
SELECT team_name AS Teams,
Count(team_name) AS no_of_matches_played,
Sum(win) AS no_of_matches_win,
Sum(draw) AS draw_matches,
( Count(team_name) - Sum(win) ) - Sum(draw) AS no_of_matches_Loss,
( Sum(win) * 2 ) + Sum(draw) AS total_points
FROM table1
GROUP BY team_name
ORDER BY no_of_matches_played DESC,
no_of_matches_win DESC
Ankit tried to solve with the draw match scenario you given at the end of the video. Solution below:
with cte as(select team_1 as team,
case when winner=team_1 then 1 else 0 end as win_flag,
case when winner='DRAW' then 1 else 0 end as draw_flag
from icc_world_cup23
union all
select team_2,
case when winner=team_2 then 1 else 0 end as win_flag,
case when winner='DRAW' then 1 else 0 end as draw_flag
from icc_world_cup23)
select team,count(*) as matches_played,
sum(win_flag) as won,count(*)-(sum(win_flag)+sum(draw_flag)) as lost,sum(draw_flag) as no_result,
sum(win_flag)*2 as points
from cte
group by team
order by won desc
Hi, for Draw flag the point should be added as 1. with your query the points are not correct
great one !!!!
my solution:
with cte as (SELECT team_1 as team,sum(case when winner = team_1 then 1 else 0 end) as winner,
sum(case when winner = 'DRAW' then 1 else 0 end) as draw_matches,
count(*) as match_played
from icc_world_cup
group by team_1
union all
SELECT team_2 as team,sum(case when winner = team_2 then 1 else 0 end) as winner,
sum(case when winner = 'DRAW' then 1 else 0 end) as draw_matches,
count(*) as match_played
from icc_world_cup
group by team_2)
select team,sum(match_played) as match_played,
sum(winner) as no_of_win_match,
sum(match_played) - sum(winner) as no_of_los_match,
sum(draw_matches) as no_of_draw_matches,
(sum(winner) * 2) + sum(draw_matches) as points
from cte
group by team
order by (sum(winner) * 2) + sum(draw_matches) desc;
Hi, With this query the Draw match is considered as Loss match.
select team, count(team) as total_matches_played, sum(win) as win, count(team) - sum(win) as loss, sum(draw) as draw, sum(win)*2 + sum(draw) as points
from
(
select team_1 as team, case when team_1 = winner then 1 else 0 end as win, case when winner = 'Draw' 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, case when winner = 'Draw' then 1 else 0 end as Draw from icc_world_cup) a
group by team
order by points desc
with all_matches as (
select team, sum(matches_played) as matches_played, sum(win_flag) as wins,sum(draw_flag) as draws from
(
select team_1 as team, count(*) as matches_played
, sum(case when team_1=winner then 1 else 0 end) as win_flag
, sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag
from icc_world_cup group by team_1
union all
select team_2 as team, count(*) as matches_played
, sum(case when team_2=winner then 1 else 0 end) as win_flag
, sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag
from icc_world_cup group by team_2) A
group by team
)
select *, matches_played-wins-draws as losses, wins*2+draws*1 as pts
from all_matches
order by wins desc,losses
Hi Ankit, I was late but I came here with my approach 🙂.
with res as
(
select Team_1,Winner from icc_world_cup
union all
select Team_2,Winner from icc_world_cup
), srt as
(
select Team_1,count(*) as No_Matches,
sum(case when Team_1 = Winner then 1 else 0 end) as Wins,
sum(case when winner = 'Draw' then 1 else 0 end) as NR
from res group by Team_1
)
select srt.*, srt.No_Matches - srt.Wins loss, srt.NR + srt.wins*2 as Points from srt
i think , to calculate the losses you should include that in losses column as well.
with cte1 as
(
select team1,winner from icc_world_cup
union all
select team2,winner from icc_world_cup
),
cte2 as (select team1,count(*) as matches_played,sum(case when team1=winner then 1 else 0 end) as wins,
sum(case when winner='Draw' then 1 else 0 end) as NR from cte1 group by team1)
select cte2.*,cte2.matches_played-cte2.wins-cte2.NR as losses,cte2.NR+cte2.wins*2 as points from cte2
select team_1,
count(*) as P,
sum(case when winner = team_1 then 1 else 0 end) as W,
sum(case when winner = team_2 then 1 else 0 end) as L,
sum(case when winner = 'DRAW' then 1 else 0 end) as D,
sum(case when winner = team_1 then 1 else 0 end)*2 as Pts
from (select team_1,team_2,winner from Icc_world_cup
union all
select team_2,team_1,winner from Icc_world_cup)
group by team_1;
select * from Icc_world_cup;
Here's my Solution for including Draw
with played_match as(
select team, sum(played) as played, sum(draw) as draw from(
select team_1 as team, count(*) as played, count(case when winner = 'DRAW' then team_1 end) as draw from icc_world_cup group by team_1
union all
select team_2 as Team, count(*) as played,count(case when winner = 'DRAW' then team_2 end) as draw from icc_world_cup group by team_2)
group by team)
,won_match as(
select winner, count(*) as won from icc_world_cup where winner 'DRAW' group by winner
)
select p.team as teams, played as P, nvl(won,0) as W, played- nvl(won,0) - NVL(Draw,0) as L, NVL(Draw,0) as D, (nvl(won,0) * 2) + NVL(Draw,0) as Pts
from played_match p left join won_match w on p.team = w.winner;
with all_matches as(
select team,
sum(matches_played) as matches_played,
sum(win_flag) as wins,
sum(draw_flag) as draws
from(
select
team_1 as team,
count(*) as matches_played,
sum(case when team_1 = winner then 1 else 0 end) as win_flag,
SUM(CASE WHEN winner ='DRAW' THEN 1 ELSE 0 END) AS draw_flag
from icc_world_cup
group by team_1
union all
select
team_2 as team,
count(*) as matches_played,
sum(case when team_2 = winner then 1 else 0 end) as win_flag,
SUM(CASE WHEN winner ='DRAW' THEN 1 ELSE 0 END) AS draw_flag
from icc_world_cup
group by team_2
)a
group by team
)
select *,
matches_played-wins-draws as losses,
wins*2 + draws as pts
from all_matches
order by wins desc
Hello sir, my name is Amit I want to learn SQL from basic to advance. Could you teach me?
Yes, sure
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
with match_result as (
select team,
sum(case when result = 'win' then 1 else 0 end) as wins,
sum(case when result = 'loss' then 1 else 0 end) as losses,
sum(case when result = 'draw' then 1 else 0 end) as draws,
count(*) as played
from (
select team_1 as team,
case when winner = team_1 then 'win'
when winner = team_2 then 'loss'
else 'draw'
end as result
from icc_world_cup
union all
select team_2 as team,
case when winner = team_2 then 'win'
when winner = team_1 then 'loss'
else 'draw'
end as result
from icc_world_cup) as x
group by team )
select team, played, wins, losses, draws, (wins * 2 + draws) as points
from match_result
order by points desc, team;
with cte as((select team_1 as teams,count(*) as matches_played,
sum(case when team_1=winner then 1 else 0 end) as win,
sum(case when winner='DRAW' then 1 else 0 end) as draw
from icc_world_cup
group by team_1)
union
(select team_2 as teams,count(*) as matches_played,
sum(case when team_2=winner then 1 else 0 end) as win,
sum(case when winner='DRAW' then 1 else 0 end) as draw
from icc_world_cup
group by team_2) )
select teams,sum(matches_played) as matches_played,sum(win) as wins,sum(matches_played-win-draw) as loss,sum(draw) as draw,sum((win*2+draw*1)) as pts
from cte
group by teams
order by pts desc
(12,'SA','IND','DRAW')
sir in this last column 13 should be there instead of 12
Yes
Assignment Solution:-
with cte as (select distinct team_1 as team, count(*) as matches_played,
sum(case when team_1=winner then 1 else 0 end) as Wins,
sum(case when winner='DRAW' then 1 else 0 end) as Draws
from icc_world_cup group by team_1
union all
select distinct team_2 as team, count(*), sum(case when team_2=winner then 1 else 0 end) as Wins,
sum(case when winner='DRAW' then 1 else 0 end) as Draws from icc_world_cup group by team_2)
select *, matches_played - wins as losses, wins*2 as pts from cte order by wins desc
with cte1(Teams) as(
select distinct team_1 from icc_world_cup
union
select distinct team_2 from icc_world_cup),
cte2 as(
select a.Teams, count(*) as P, count(case when b.winner = a.Teams then 1 else null end) as W,
count(case when b.winner = 'DRAW' then 1 else null end) as NR
from cte1 a join icc_world_cup b
on a.Teams = b.team_1 or a.Teams = b.team_2
group by a.Teams)
select Teams, P, W, P-W as L, NR,
case
when NR = 0 then W*2
else W*2+NR
end as 'Pts'
from cte2
order by Pts desc
Hi sir, added an extra column where it mentions 'NR' for matches tied else '-' :- WITH CTE1 AS
(SELECT Team, sum(match_played) as Matches, sum(wins) as Won, sum(Draw) as NR, CASE WHEN Draw = 1 THEN 'NR' ELSE NULL END AS Draws FROM(
SELECT team_1 as Team, count(*) as match_played,
SUM(CASE WHEN team_1 = winner THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS Draw FROM icc_world_cup_2023 group by team_1
union all
SELECT team_2 as Team, count(*) as match_played,
SUM(CASE WHEN team_2 = winner THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS Draw FROM icc_world_cup_2023 group by team_2) AS A
GROUP BY Team, Draw)
SELECT Team, Matches, Won, CASE WHEN Draws = 'NR' THEN '0' ELSE (Matches-Won) END as Lost, coalesce(Draws, '-') as Tied,(Won*2)+NR as Points FROM CTE1
ORDER BY Points desc
Great job 👍
My Approach:
with cte as(select team_1,
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,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)
select team_1,count(*) as match_played, sum(win_flag) as won,
count(*) - (sum(win_flag)+sum(draw_flag)) as loss,
sum(draw_flag) draw,
2*sum(win_flag) + sum(draw_flag) as pts
from cte group by team_1
select
team,
sum(matches_played) as matches_played,
sum(wins) as wins,
sum(matches_played)-sum(wins) as losses,
sum(draws) as draw_matches,
sum(wins)*2 as Pts
from
(
select
team_1 as team, count(team_1) as matches_played,
sum(case when winner=team_1 then 1 else 0 end) as wins,
sum (case when winner='DRAW' then 1 else 0 end) as draws
from Icc_world_cup
group by team_1
union all
select
team_2 as team, count(team_2) as matches_played,
sum(case when winner=team_2 then 1 else 0 end) as wins,
sum (case when winner='DRAW' then 1 else 0 end) as draws
from Icc_world_cup
group by team_2
) t
group by team
order by wins desc;
with cte as(
select team,sum(match_played) as match_played,sum(win_flag) as wins,
sum(NR) as NR from(
select team_1 as team,count(*) as match_played ,
sum(case when winner=team_1 then 1 else 0 end) as win_flag,
sum(case when winner='DRAW' then 1 else 0 end ) as NR
from icc_world_cup group by team_1
union all
select team_2 as team, count(*) as match_played,
sum(case when winner=team_2 then 1 else 0 end) as win_flag,
sum(case when winner='DRAW' then 1 else 0 end ) as NR
from icc_world_cup group by team_2)a
group by team)
select * ,match_played-wins,wins*2+NR*1 as pts
from cte
order by wins desc;
Great job 👏
Homework solution:-
with cte as
(
Select distinct team_1 as team, count(*) as played_matches, sum(case when team_1=winner then 1 else 0 end) as wins,sum(case when winner='DRAW' then 1 else 0 end) as No_result from icc_world_cup group by team_1
union all
Select distinct team_2 as team, count(*) as played_matches, sum(case when team_2=winner then 1 else 0 end) as wins,sum(case when winner='DRAW' then 1 else 0 end) as No_result from icc_world_cup group by team_2
)
Select team, sum(played_matches) as plays, sum(wins) as wins,
(sum(played_matches)-sum(wins)-sum(no_result)) as loss,
sum(no_result) as no_result,
(sum(wins)*2 +sum(no_result)*1) as pts from cte
group by team
order by pts desc;
with mt as (
SELECT team_1 as team,winner from icc_world_cup
union all
SELECT team_2 as team,winner from icc_world_cup
)
select team,count(team) Matchs_played,
sum(case when team = winner then 1 else 0 end ) as Win,
sum(case when winner = 'DRAW' then 1 else 0 end ) as Draw,
count(team) - sum(case when team = winner then 1 else 0 end ) - sum(case when winner = 'DRAW' then 1 else 0 end ) as lost,
#sum(case when team != winner and team != 'DRAW' then 1 else 0 end ) as lost,
sum(case when team = winner then 1 else 0 end) * 2 as Points
from mt
group by team
select team_1 as team,sum(cnt) matches ,sum(win_flag) no_wins, (sum(cnt)-sum(win_flag)-sum(dr)) lose, sum(dr) draw
,sum(win_flag)*2+sum(dr)*1 draw
from (
select team_1 ,count(team_1) cnt,count(
case when team_1=winner then 1 end) win_flag
,sum(case when winner='draw' then 1 else 0 end ) dr
from icc_world_cup
group by team_1
union all
select team_2 , count(team_2) cnt, count(
case when team_2=winner then 1 end) win_flag,
sum(case when winner='draw' then 1 else 0 end ) dr
from icc_world_cup
group by team_2) k
group by team_1
with cte as (
(select team_1 as team, count(*) as matches_played, sum(case when team_1 = winner then 1 else 0 end) as wins, sum(case when winner = 'DRAW' then 1 else 0 end) as ties from icc_world_cup group by team_1
union all
select team_2 as team, count(*) as matches_played, sum(case when team_2 = winner then 1 else 0 end) as wins, sum(case when winner = 'DRAW' then 1 else 0 end) as ties from icc_world_cup group by team_2))
select team, sum(matches_played) as matches_played, sum(wins) as wins, matches_played - wins as losses , ties, (wins * 2) + ties as pts from cte group by team order by pts desc
with cte as(
select a.teams,sum(MatchPlayed)MatchPlayed,sum(b)winns,sum(draws)draws
from (
select team_1 teams,count(*) MatchPlayed,sum(case when team_1=winner then 1 else 0 end)b,sum(case when winner = 'DRAW' then 1 else 0 end)draws
from icc_world_cup group by team_1
union all
select team_2,count(*),sum(case when team_2=winner then 1 else 0 end)a ,sum(case when winner = 'DRAW' then 1 else 0 end)draws
from icc_world_cup group by team_2)a
group by a.teams)
select teams,MatchPlayed,winns,(MatchPlayed-winns) loss,draws,((winns*2)+draws)Points
from cte
my approach:
with cte as(
select team,sum(matches_played) as matches_played,sum(w) as W,sum(draw) as Draw from
((select team_1 as team,
count(*) as matches_played,
sum(case when team_1=winner then 1 else 0 end) as W,
sum(case when winner = "draw" then 1 else 0 end) as Draw
from icc_world_cup group by team_1)
union all
(select team_2,
count(*) as matches_played,
sum(case when team_2=winner then 1 else 0 end) as W ,
sum(case when winner = "draw" then 1 else 0 end) as Draw
from icc_world_cup group by team_2)) a
group by team
)
select team,W,(matches_played - W) as L,draw,(W*2+Draw) as pts
from cte order by pts desc
WITH CTE AS (
SELECT team_1 as TEAMS, COUNT(*) AS PLAYED ,sum(case when winner=team_1 then 1 else 0 end) as won
,sum(case when winnerteam_1 and winnerteam_2 then 1 else 0 end) as DRAW FROM ICC_WORLD_CUP
GROUP BY TEAM_1
union
SELECT team_2 AS TEAMS,COUNT(*) AS PLAYED,sum(case when winner=team_2 then 1 else 0 end) as won
,sum(case when winnerteam_1 and winnerteam_2 then 1 else 0 end) as DRAW FROM ICC_WORLD_CUP
GROUP BY TEAM_2 )
SELECT TEAMS,SUM(PLAYED) as p,SUM(won) as w ,SUM(DRAW) as D ,SUM(PLAYED)-(SUM(won) +SUM(DRAW)) AS L ,SUM(won)*2 + SUM(DRAW)*1 AS POINTS FROM CTE GROUP BY TEAMS
21 times ‘right’ till 3.15min
Thanks for counting 😄
;with cte as(
select team_1 as team,count(*) as matches_played,sum(case when team_1=winner then 1 else 0 end) as win_flag,
sum(case when winner='draw' then 1 else 0 end)as draw_flag
from icc_worldcup group by team_1
union all
select team_2 as team,count(*) as matches_played,sum(case when team_2=winner then 1 else 0 end) as win_flag,
sum(case when winner='draw' then 1 else 0 end)as draw_flag
from icc_worldcup group by team_2)
select team,sum(matches_played) as matches_played ,sum(win_flag) as wins,
sum(matches_played)-sum(win_flag)-sum(draw_flag) as losses,
sum(draw_flag) as draws,
sum(win_flag)*2+sum(draw_flag)*1 as pnts
from cte
group by team
with match_details as (
select players, sum(matches_played) total_matches, sum(win_flag) as wins, sum(draw_flag) as draws
from (
select team_1 as players, count(*) matches_played,
sum(case when team_1 = winner then 1 else 0 end) as win_flag,
sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag
from icc_world_cup
--where winner != 'DRAW'
group by team_1
union all
select team_2, count(*) matches_played,
sum(case when team_2 = winner then 1 else 0 end) as win_flag,
sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag
from icc_world_cup
group by team_2 ) a
group by players
)
select *, total_matches-wins as losses, wins*2+draws as points
from match_details
order by points desc
with All_matches as (
Select team_1 as Team, count(*) as matches_played,
sum(case when team_1 = winner then 1 else 0 END) as Wins,
sum(case when winner = 'DRAW' then 1 else 0 END) as Draws
from icc_world_cup
group by team_1
Union
Select team_2 as Team, count(*) as matches_played,
sum(case when team_2 = winner then 1 else 0 END) as wins,
sum(case when winner = 'DRAW' then 1 else 0 END) as Draws
from icc_world_cup
group by team_2)
Select Team, sum(matches_played) as Matches_played, sum(Wins) as wins, sum(Draws) as Draws,
sum(matches_played) - (sum(Wins) + sum(Draws)) as Losses, (sum(Wins)*3+sum(Draws)*1) as Points
from All_matches
group by Team
order by 6 desc
with teams as (select team_1 from icc_world_cup
union ALL
select team_2 from icc_world_cup),
team_count as (
select teams.team_1 as team, count(teams.team_1) as total_matches from teams
GROUP by teams.team_1),
winner_count as (select winner, count(winner) as winners from icc_world_cup
group by winner)
select team, total_matches
, COALESCE(winners,0) as wins
from team_count
left join winner_count on team_count.team=winner_count.winner
with cte_tbl as (
select team_1,
case when team_1=winner then 1 end as checksW,
case when team_1!=winner then 1 end as checksL,
case when winner='DRAW' then 1 end as checksD
from icc_world_cup2023
union all
select team_2,
case when team_2=winner then 1 end as checksW,
case when team_2!=winner then 1 end as checksL,
case when winner='DRAW' then 1 end as checksD
from icc_world_cup2023)
select team_1 as Teams, count(checksW) as W, count(checksL) as L , count(checksD) as D ,
case when count(checksD)=1 then count(checksW)*2+1 else count(checksW)*2 end as Pts
from cte_tbl
group by team_1
order by Pts desc;
select team, sum(total_match) total_match_played, SUM(matches) as actual_win,sum(total_match) - SUM(matches) as loss from
(
select team_1 as team, COUNT(*) as total_match, SUM(case when team_1 = winner then 1 else 0 end ) as matches
from icc_world_cup group by team_1
union all
select team_2 as team, COUNT(*) as total_match, SUM(case when team_2 = winner then 1 else 0 end ) as matches
from icc_world_cup group by team_2 ) k
group by team
order by actual_win desc
with cte as (
select team,sum(match_played) as total_match,sum(win_cnt) as Wins,
sum(DRAW_CNT) as Ties from
(
select team_1 as team, count(*) as match_played,
sum(CASE
WHEN team_1=winner THEN 1 else 0
END) as win_cnt,
sum(CASE
WHEN winner='DRAW' THEN 1 else 0
END) as DRAW_CNT
from Icc_world_cup
group by team_1
union all
select team_2 as team, count(*) as match_played,
sum(CASE
WHEN team_2=winner THEN 1 else 0
END) as win_cnt,
sum(CASE
WHEN winner='DRAW' THEN 1 else 0
END) as DRAW_CNT
from Icc_world_cup
group by team_2
) a
group by team
)
select * ,(total_match-Wins) as Loss ,
(Wins*2+Ties*1) as Pts
from cte
order by Wins desc
;🏏🏏🏏🏏
select TEAM, count(TEAM) as T,COUNT(W) AS W,COUNT(L) AS L,COUNT(D) AS D,COUNT(W)*2+COUNT(D) AS P
from (
SELECT TEAM_1 AS TEAM,
CASE WHEN WINNER=TEAM_1 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W ,
CASE WHEN WINNER=TEAM_1 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L ,
CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D
FROM icc_world_cup
UNION ALL
SELECT TEAM_2 AS TEAM,
CASE WHEN WINNER=TEAM_2 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W,
CASE WHEN WINNER=TEAM_2 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L ,
CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D
FROM icc_world_cup ) AS WC
group by TEAM;
WITH all_matches AS (
SELECT team, SUM(matches_played) AS matches_played, SUM(win_flag) AS wins, SUM(NR_flag) AS no_result FROM (
SELECT team_1 AS team, count(*) AS matches_played,
SUM(CASE WHEN team_1 = winner THEN 1 ELSE 0 END) AS win_flag,
SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS NR_flag
FROM icc_world_cup GROUP BY team_1
UNION ALL --to remove duplicates so that sum comes together
SELECT team_2 AS team, count(*) AS matches_played,
SUM(CASE WHEN team_2 = winner THEN 1 ELSE 0 END) AS win_flag,
SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS NR_flag
FROM icc_world_cup GROUP BY team_2
) A GROUP BY team
)
SELECT *, matches_played-wins-no_result AS losses, (wins*2)+(no_result*1) AS Pts FROM all_matches
ORDER BY wins DESC
WITH CTE AS
(
SELECT team_1 AS team, winner
FROM icc_world_cup_2023
UNION ALL
SELECT team_2 AS team, winner
FROM icc_world_cup_2023
)
SELECT
team,
COUNT(*) AS total_matches,
SUM(CASE WHEN team = winner THEN 1 ELSE 0 END) AS matches_won,
COUNT(*) - SUM(CASE WHEN team = winner THEN 1
WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS matches_lost,
COUNT(CASE WHEN winner = 'DRAW' THEN 1 END) AS no_result,
SUM(CASE WHEN team = winner THEN 2
WHEN winner = 'DRAW' THEN 1
ELSE 0 END) AS total_points
FROM CTE
GROUP BY team
ORDER BY total_points DESC;
with all_matches
as
(select team, sum(matches_played) as matches_played, sum(win_flag) as wins
,sum(draw_flag) as draws
from
(
select (team_1) as team, count(*) as matches_played,
sum(case when team_1=winner then 1 else 0 end) as win_flag,
sum(case when winner= 'DRAW' then 1 else 0 end) as draw_flag
from icc_world_cup
group by team_1
union all
select (team_2) as team, count(*) as matches_played
,sum(case when team_2=winner then 1 else 0 end) as win_flag
,sum(case when winner='DRAW' then 1 else 0 end) as draw_flag
from icc_world_cup
group by team_2
) A
group by team
)
select *, matches_played - wins as loss,
(CASE when draws>0 then wins*2 +draws else wins*2 end) as pts
from all_matches
with cte as(
select team,sum(match_played) as match_played,sum(win_flag) as wins,
sum(NR) as NR from(
select team_1 as team,count(*) as match_played ,
sum(case when winner=team_1 then 1 else 0 end) as win_flag,
sum(case when winner='DRAW' then 1 else 0 end ) as NR
from icc_world_cup group by team_1
union all
select team_2 as team, count(*) as match_played,
sum(case when winner=team_2 then 1 else 0 end) as win_flag,
sum(case when winner='DRAW' then 1 else 0 end ) as NR
from icc_world_cup group by team_2)a
group by team)
select * ,match_played-wins,wins*2+NR*1 as pts
from cte
order by wins desc;