Partnerships: cutting a sequence into sessions

Some questions don't look for a rare pattern. They cut the whole sequence into pieces: website visits into sessions, sensor readings into runs, an innings into partnerships. A partnership is everything two batters do together, from one wicket to the next (and, like running between the wickets, it ends badly when one says "yes" and the other says "no"). Every ball belongs to exactly one partnership.

The test case

For the first innings of the 2025 final (match 16), list each partnership: its number, the two batters, the runs (extras included, as scorers do), the legal balls, and how it ended. The last partnership may be unbroken: the innings ended with those two batters still in.

Expected result

Way 1: MATCH_RECOGNIZE

select m.partnership,
       b1.player_name || ' & ' || b2.player_name as batters,
       m.runs, m.balls, m.ended
from   (select * from cr_balls_v where match_id = 16 and innings_no = 1)
match_recognize (
  order by seq_no
  measures match_number()                      as partnership,
           first(batter_id)                    as first_batter,
           first(non_striker_id)               as second_batter,
           sum(total_runs)                     as runs,
           sum(is_legal)                       as balls,
           nvl(last(wicket_kind), 'unbroken')  as ended
  pattern (ball* out | ball+)
  define ball as is_wicket = 0,
         out  as is_wicket = 1
) m
join   cr_players b1 on b1.player_id = m.first_batter
join   cr_players b2 on b2.player_id = m.second_batter
order  by m.partnership;

How it cuts:

• ball* out: any number of balls without a wicket, then the wicket ball. That's a finished partnership, and the wicket ball belongs to it (the batter was out during it).
• | ball+: or, at the very end, balls with no wicket to close them: the unbroken partnership. Alternation tries the left side first, so this branch is only used when there's no wicket left.
• MATCH_NUMBER() numbers the partnerships 1, 2, 3... for free.
• FIRST(batter_id) and FIRST(non_striker_id) are the two batters at the start of the partnership, and it's the same two until it ends.
• LAST(wicket_kind) is the wicket on the last row, or null when the last row isn't a wicket.

There's no PARTITION BY here because we picked one innings in the WHERE. For every innings of the league, you'd add partition by match_id, innings_no.

Way 2: count the wickets that came before

Number each ball by how many wickets had fallen before it. Every ball of the first partnership gets 0, the second partnership gets 1, and so on. Then it's an ordinary GROUP BY.

with numbered as (
  select b.*,
         sum(is_wicket) over (order by seq_no) - is_wicket as wickets_before
  from   cr_balls_v b
  where  match_id = 16 and innings_no = 1
),
partnerships as (
  select wickets_before + 1                                                as partnership,
         min(batter_id)      keep (dense_rank first order by seq_no)        as first_batter,
         min(non_striker_id) keep (dense_rank first order by seq_no)        as second_batter,
         sum(total_runs)                                                   as runs,
         sum(is_legal)                                                     as balls,
         nvl(max(wicket_kind), 'unbroken')                                 as ended
  from   numbered
  group  by wickets_before
)
select p.partnership,
       b1.player_name || ' & ' || b2.player_name as batters,
       p.runs, p.balls, p.ended
from   partnerships p
join   cr_players b1 on b1.player_id = p.first_batter
join   cr_players b2 on b2.player_id = p.second_batter
order  by p.partnership;

The trick is "before": sum(is_wicket) over (order by seq_no) counts wickets up to and including this ball, so subtracting this ball's own is_wicket gives the wickets before it, and the wicket ball keeps the old number. (The same thing, written as a window frame: nvl(sum(is_wicket) over (order by seq_no rows between unbounded preceding and 1 preceding), 0).)

MIN(batter_id) KEEP (DENSE_RANK FIRST ORDER BY seq_no) is "the batter on the partnership's first ball", the same job FIRST(batter_id) does in Way 1.

Pick Way 1 when sessions have a real shape (start, middle, end markers); Way 2 when "a new group starts at every X" is all there is to it. Both are standard answers, and Part 13 uses the same trick for website sessions ("a new session after 30 minutes of nothing").

19c and 26ai

The same on both.

Gotchas

• Which piece does the boundary row belong to? The wicket ball ends the partnership, so it belongs to the one that ended. In Way 1 that's ball* out; in Way 2 it's the "before" in wickets_before. Get this wrong and every partnership is off by one ball.
• Don't forget the last piece. Without | ball+, the unbroken partnership would simply be missing.
• Run outs of the non-striker still end a partnership. Our is_wicket counts every dismissal, which is what we want here.

Try it yourself

What were the three highest partnerships in the whole league? Show the match, the batters, the runs and the balls.

select m.match_id,
       b1.player_name || ' & ' || b2.player_name as batters,
       m.runs, m.balls
from   cr_balls_v
match_recognize (
  partition by match_id, innings_no
  order by seq_no
  measures first(batter_id)      as first_batter,
           first(non_striker_id) as second_batter,
           sum(total_runs)       as runs,
           sum(is_legal)         as balls
  pattern (ball* out | ball+)
  define ball as is_wicket = 0,
         out  as is_wicket = 1
) m
join   cr_players b1 on b1.player_id = m.first_batter
join   cr_players b2 on b2.player_id = m.second_batter
order  by m.runs desc, m.balls
fetch  first 3 rows only;