Gaps and islands: a bowler's spells

"Gaps and islands" is the name for any question about runs of consecutive values: unbroken login streaks, ranges of free seat numbers, periods a machine was running. The trick is always to find a value that stays the same within a run and changes between runs, then group by it.

Cricket gives it a twist. A bowler can't bowl two overs in a row; a spell is a run of overs from the same end: 3, 5, 7, 9. The step is 2, not 1.

The test case

Leeds's bowling in the first innings of the 2026 final (match 32): each bowler's spells, with the first and last over of each and how many overs it lasted.

Expected result

Every bowler gets their four overs in two or three bursts. Freddie Lowe opens with one over, returns for the 12th, and comes back for the 16th and 18th at the death: the captain kept his best yorkers for last.

Way 1: subtract a row number ("tabibitosan")

with overs as (
  select distinct bowler_id, over_no from cr_balls_v where match_id = 32 and innings_no = 1
),
grouped as (
  select bowler_id, over_no,
         over_no - 2 * row_number() over (partition by bowler_id order by over_no) as grp
  from   overs
)
select p.player_name as bowler, min(g.over_no) as first_over, max(g.over_no) as last_over,
       count(*) as overs
from   grouped g
join   cr_players p on p.player_id = g.bowler_id
group  by p.player_name, g.grp
order  by first_over;

Inside a spell the overs go up by 2 and 2 × row_number goes up by 2, so their difference is constant. A gap makes the over jump by more, and the difference changes. Group by it. (Tabibitosan is Japanese for "traveller's calculation". It sounds better than "subtract row_number".)

Way 2: flag where each run starts, then count the flags

with overs as (
  select distinct bowler_id, over_no from cr_balls_v where match_id = 32 and innings_no = 1
),
flagged as (
  select bowler_id, over_no,
         case when over_no - lag(over_no) over (partition by bowler_id order by over_no) = 2
              then 0 else 1 end as new_spell
  from   overs
),
numbered as (
  select f.*, sum(new_spell) over (partition by bowler_id order by over_no) as spell_no
  from   flagged f
)
select p.player_name as bowler, min(n.over_no) as first_over, max(n.over_no) as last_over,
       count(*) as overs
from   numbered n
join   cr_players p on p.player_id = n.bowler_id
group  by p.player_name, n.spell_no
order  by first_over;

LAG compares each over with the bowler's previous one: 2 later means "same spell", anything else (or no previous over) starts a new one. A running SUM of the flags numbers the spells. More steps, but it works for any rule ("within 10 minutes", "same session"), not just fixed steps.

Way 3: MATCH_RECOGNIZE

select p.player_name as bowler, m.first_over, m.last_over, m.overs
from   (select distinct bowler_id, over_no from cr_balls_v where match_id = 32 and innings_no = 1)
match_recognize (
  partition by bowler_id
  order by over_no
  measures first(over_no) as first_over, last(over_no) as last_over, count(*) as overs
  pattern (strt nxt*)
  define nxt as over_no = prev(over_no) + 2
) m
join   cr_players p on p.player_id = m.bowler_id
order  by m.first_over;

"A start, then any number of overs each exactly 2 after the previous one." Part 6 has the rest of MATCH_RECOGNIZE.

19c and 26ai

The same on both.

Gotchas

• Duplicates break tabibitosan. One row per bowler per over is needed, hence the DISTINCT: CR_BALLS_V has a row per ball.
• The gap rule is the whole problem. Here "2 overs later". For dates it might be "the next day", for timestamps "within 30 minutes": Way 2 adapts to any of them.