https://www.youtube.com/watch?v=t7mkWPga8lI&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=18

CREATE TABLE icc_world_cup (
    match_no INT,
    team_1 VARCHAR(20),
    team_2 VARCHAR(20),
    winner VARCHAR(20)
);

INSERT INTO icc_world_cup (match_no, team_1, team_2, winner) VALUES
(1, 'ENG', 'NZ', 'NZ'),
(2, 'PAK', 'NED', 'PAK'),
(3, 'AFG', 'BAN', 'BAN'),
(4, 'SA', 'SL', 'SA'),
(5, 'AUS', 'IND', 'IND'),
(6, 'NZ', 'NED', 'NZ'),
(7, 'ENG', 'BAN', 'ENG'),
(8, 'SL', 'PAK', 'PAK'),
(9, 'AFG', 'IND', 'IND'),
(10, 'SA', 'AUS', 'SA'),
(11, 'BAN', 'NZ', 'NZ'),
(12, 'PAK', 'IND', 'IND');

UNION_UNION ALL
COALESCE()

-- Approach 1
WITH ALL_Matches AS
(
SELECT TeamName,SUM(Matches_Played) AS Matches_Played
FROM(
SELECT team_1 AS TeamName,COUNT(*) AS Matches_Played FROM icp
GROUP BY team_1
UNION ALL
SELECT team_2,COUNT(*) AS Matches_Played FROM icp
GROUP BY team_2
) MP
GROUP BY TeamName
)
,Winner_cte AS (
SELECT Winner,COUNT(*) AS Wins FROM icp
GROUP BY Winner)

SELECT am.TeamName,am.matches_played,COALESCE(wc.wins,0) AS Wins, am.Matches_Played - COALESCE(wc.wins,0) AS Loses,COALESCE(Wins,0)*2 AS Points 
FROM All_matches am LEFT JOIN Winner_cte wc ON am.Teamname = wc.Winner
ORDER BY Wins Desc;

CASE WHEN

-- Approach 2, No join used, Also there was no need for null handling here, Also just 1 cte.
WITH ALL_Matches AS
(
SELECT TeamName,SUM(Matches_Played) AS Matches_Played,SUM(Win_Flag) AS Wins
FROM(
SELECT team_1 AS TeamName,COUNT(*) AS Matches_Played,
SUM(CASE WHEN team_1 = winner THEN 1 ELSE 0 END) AS Win_flag FROM icp
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 Win_Flag FROM icp
GROUP BY team_2
) MP
GROUP BY TeamName
)
SELECT *, Matches_Played - Wins AS Losses, Wins* 2 AS Pts FROM All_matches ORDER BY Wins DESC;