Practice: three window-function problems

with overs as (
  select t.team_code, b.match_id, b.over_no, sum(b.total_runs) as runs
  from   cr_balls_v b
  join   cr_teams t on t.team_id = b.batting_team_id
  where  b.season = 2026
  group  by t.team_code, b.match_id, b.over_no
)
select team_code, match_id, over_no, runs
from   (select o.*,
               row_number() over (partition by team_code order by runs desc, match_id, over_no) as rn
        from   overs o)
where  rn = 1
order  by team_code;

with overs as (
  select t.team_code, b.match_id, b.over_no, sum(b.total_runs) as runs
  from   cr_balls_v b
  join   cr_teams t on t.team_id = b.batting_team_id
  where  b.season = 2026
  group  by t.team_code, b.match_id, b.over_no
)
select team_code,
       min(match_id) keep (dense_rank first order by runs desc, match_id, over_no) as match_id,
       min(over_no)  keep (dense_rank first order by runs desc, match_id, over_no) as over_no,
       max(runs)                                                                  as runs
from   overs
group  by team_code
order  by team_code;

Top-1 per group (page 5.7), or KEEP when you only need one row and a few columns.

with overs as (
  select over_no, sum(total_runs) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 2
  group  by over_no
),
jumps as (
  select over_no, runs, runs - lag(runs) over (order by over_no) as jump
  from   overs
)
select over_no, runs, jump
from   jumps
where  jump = (select max(jump) from jumps);

26ai only

with overs as (
  select over_no, sum(total_runs) as runs
  from   cr_balls_v
  where  match_id = 32 and innings_no = 2
  group  by over_no
),
jumps as (
  select over_no, runs, runs - lag(runs) over (order by over_no) as jump
  from   overs
)
select over_no, runs, jump
from   jumps
qualify rank() over (order by jump desc nulls last) = 1;

Window functions can't be nested, so the jump is computed in its own step and ranked in the next.

The last powerplay over went for 22, twelve more than the over before. The first over has no previous over, so its jump is null; MAX ignores it, and NULLS LAST keeps it out of first place in the RANK.

with results as (
  select home_team_id as team_id,
         case when winner_id = home_team_id then 2 when winner_id is null then 1 else 0 end as pts
  from   cr_matches where season = 2026 and stage = 'LEAGUE'
  union all
  select away_team_id,
         case when winner_id = away_team_id then 2 when winner_id is null then 1 else 0 end
  from   cr_matches where season = 2026 and stage = 'LEAGUE'
)
select t.team_name,
       count(*)                                 as played,
       sum(r.pts)                               as points,
       rank() over (order by sum(r.pts) desc)   as pos
from   results r
join   cr_teams t on t.team_id = r.team_id
group  by t.team_name
order  by points desc, t.team_name;

with results as (
  select home_team_id as team_id,
         case when winner_id = home_team_id then 2 when winner_id is null then 1 else 0 end as pts
  from   cr_matches where season = 2026 and stage = 'LEAGUE'
  union all
  select away_team_id,
         case when winner_id = away_team_id then 2 when winner_id is null then 1 else 0 end
  from   cr_matches where season = 2026 and stage = 'LEAGUE'
),
standings as (
  select t.team_name, count(*) as played, sum(r.pts) as points
  from   results r join cr_teams t on t.team_id = r.team_id
  group  by t.team_name
)
select x.team_name, x.played, x.points,
       (select count(*) + 1 from standings y where y.points > x.points) as pos
from   standings x
order  by x.points desc, x.team_name;

rank() over (order by sum(r.pts) desc) is a window function over an aggregate: GROUP BY runs first, then the ranking. Chennai and Leeds finish level on 8 (real leagues would split them on net run rate; ours shrugs and says joint first).