Practice: three subquery problems

select side, count(*) as matches
from   (select case when m.winner_id is null then 'tie'
                    when (select p.team_id from cr_players p where p.player_id = m.player_of_match_id) = m.winner_id
                         then 'winning team'
                    else 'losing team' end as side
        from   cr_matches m)
group  by side
order  by side;

select case when m.winner_id is null then 'tie'
            when p.team_id = m.winner_id then 'winning team'
            else 'losing team' end as side,
       count(*) as matches
from   cr_matches m
join   cr_players p on p.player_id = m.player_of_match_id
group  by case when m.winner_id is null then 'tie'
               when p.team_id = m.winner_id then 'winning team'
               else 'losing team' end
order  by side;

Our simulator only ever gives the award to someone on the winning side, which is why the losing team never appears. Real leagues are kinder to heroic losers.

with spells as (
  select b.season, p.player_name, b.match_id,
         sum(b.is_bowler_wicket) as wickets,
         sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.bowler_id
  group  by b.season, p.player_name, b.match_id
)
select season, player_name, match_id, wickets, runs
from   (select s.*, row_number() over (partition by season order by wickets desc, runs, match_id) as rn
        from   spells s)
where  rn = 1
order  by season;

with spells as (
  select b.season, p.player_name, b.match_id,
         sum(b.is_bowler_wicket) as wickets,
         sum(b.runs_batter + case when b.extra_type in ('WD', 'NB') then b.runs_extras else 0 end) as runs
  from   cr_balls_v b
  join   cr_players p on p.player_id = b.bowler_id
  group  by b.season, p.player_name, b.match_id
)
select s.season, s.player_name, s.match_id, s.wickets, s.runs
from   spells s
where  not exists (select 1 from spells x
                   where  x.season = s.season
                   and   (x.wickets > s.wickets
                          or (x.wickets = s.wickets and x.runs < s.runs)
                          or (x.wickets = s.wickets and x.runs = s.runs and x.match_id < s.match_id)))
order  by s.season;

The second answer says "there's no better spell in the same season", with "better" spelled out as the same tie-breaking rules. If two spells tied on everything (same match too), it would return both.

select season, match_id, player_name, runs
from   (select b.season, b.match_id, p.player_name, sum(b.runs_batter) as runs
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.batter_id
        group  by b.season, b.match_id, p.player_name)
where  runs >= 100
order  by season, match_id;

with innings as (
  select b.season, b.match_id, b.batter_id, sum(b.runs_batter) as runs
  from   cr_balls_v b
  group  by b.season, b.match_id, b.batter_id
  having sum(b.runs_batter) >= 100
)
select i.season, i.match_id, p.player_name, i.runs
from   innings i
join   cr_players p on p.player_id = i.batter_id
order  by i.season, i.match_id;