create table icc_world_cup
(
Team_1 Varchar(20),
Team_2 Varchar(20),
Winner Varchar(20)
);
INSERT INTO icc_world_cup values('India','SL','India');
INSERT INTO icc_world_cup values('SL','Aus','Aus');
INSERT INTO icc_world_cup values('SA','Eng','Eng');
INSERT INTO icc_world_cup values('Eng','NZ','NZ');
INSERT INTO icc_world_cup values('Aus','India','India');

with ct as (
select *, row_number() over(order by Team_1) as id from icc_world_cup
)
, teams as (
    select Team_1 as team, id from ct
    union all
    select Team_2 as team, id from ct
)
,agg_ct as (select t.team, t.id 
    ,case when t.team = c.Winner then 1 end as winner_flag
    ,case when t.team <> c.Winner then 1 end as loser_flag
    
    from teams t
    join ct c on t.id=c.id
    )
select team, count(id) as total_match_played, sum(winner_flag) as no_of_wins, sum(loser_flag) as no_of_loss
        from agg_ct
group by team;