Oracle SQL: How to generate a cricket match point table with details such as win count, lost count etc?

Question

I faced this question in an interview. I had to get points earned , win count, lost count, match drawn count for a team. My query gives me correct result but i am looking for a way to shoterned the query. Any help ?

Certain Conditions i Considered in my query are:

1. If a team wins i am allocating 3 as match point and 2 if a team loses
2. If the match is a tie (when winner is null) i am awarding 1 point to each team.

DDL and DML:

create table match_t(team1 varchar(20),team2 varchar(20),Winner varchar(20));

insert into match_t values('India','Pakistan','India');
insert into match_t values('India','Srilanka','India');
insert into match_t values('Srilanka','Pakistan','Pakistan');
insert into match_t values('Srilanka','India','Srilanka');
insert into match_t values('Pakistan','Srilanka','Srilanka');
insert into match_t values('Pakistan','India','India');
insert into match_t values('India','Srilanka','India');
insert into match_t values('Pakistan','India',null);
insert into match_t values('Srilanka','Pakistan',null);
Commit;

My answer to the problem:

with abc as(
select team1 as host,team2 as guest,case when team1=winner
then 1 else 0 end as host_w,
case when team2 = winner
then 1 else 0 end as guest_w  
 from match_t), bac as(
 select host,3 as m_point,1 as host_win,0 as guest_win,0 as match_d from abc where host_w > guest_w
 union all
 select guest,3 as m_point,0 as host_win,1 as guest_win,0 as match_d from abc where host_w < guest_w
union all
select guest,2 as m_point,0 as host_win,0 as guest_win,0 as match_d from abc where host_w > guest_w
 union all
 select host,2 as m_point,0 as host_win,0 as guest_win,0 as match_d from abc where host_w < guest_w
 union all
 select host,1 as m_point,0 as host_win,0 as guest_win, 1 as match_d from abc where host_w = guest_w
 union all
 select guest,1 as m_point,0 as host_win,0 as guest_win, 1 as match_d from abc where host_w = guest_w
 ),
 cad as(
 select host as team,sum(m_point) as match_p,sum(host_win+guest_win) as win_c,
sum(match_d)  as match_d_c 
 from bac group by host),
 dac as(select sum(lost_c) as lost_c,team from (select count(*) as lost_c,host as team from abc where host_w=0 and guest_w <> 0
 group by host
 union all
 select count(*) as lost_c,guest as team from abc where guest_w=0 and host_w <> 0
 group by guest) group by team)
  select a.team,a.match_p,a.win_c,b.lost_c,a.match_d_c,a.win_c+b.lost_c+a.match_d_c as no_match from cad a, dac b where a.team=b.team

It gives me correct result(refer below) . But I am looking for a way how easily i can get it without writing such long code

Answer 1

I would do this using union all, but the query would simply be:

select team, sum(is_win) as num_wins, sum(is_loss) as num_losses, sum(is_tie) as num_ties
from ((select team1 as team,
               (case when winner = team1 then 1 else 0 end) as is_win,
               (case when winner = team2 then 1 else 0 end) as is_loss,
               (case when winner is null then 1 else 0 end) as is_tie
        from match_t
       ) union all
       (select team2,
               (case when winner = team2 then 1 else 0 end) as is_win,
               (case when winner = team1 then 1 else 0 end) as is_loss,
               (case when winner is null then 1 else 0 end) as is_tie
        from match_t
       )
      ) t
group by team;

I'm a bit surprised at how complicated the other answers are. The idea is pretty simple. For each team in the match you want flags indicating whether the game was a win, loss, or tie. You then want to aggregate these flags over all the teams.