The longest spells of dot balls

A run of dot balls is a team stuck: no runs, pressure building. If one dot ball is a team holding its breath, ten in a row is a team turning blue. This page uses that question to look closely at what ONE ROW PER MATCH gives you back, and the mistake almost everyone makes the first time.

The test case

Find every spell of seven or more dot balls in a row in an innings. Show the match, the innings, the batting team's code, the first and last ball, and the number of dots.

"In a row" means consecutive deliveries in the innings, whoever faced them. A wide is a run, so it ends a spell. A wicket ball with no runs is still a dot.

Expected result

Way 1: MATCH_RECOGNIZE

select m.match_id, m.innings_no, t.team_code, m.from_ball, m.to_ball, m.dots
from   cr_balls_v
match_recognize (
  partition by match_id, innings_no, batting_team_id
  order by seq_no
  measures first(over_ball) as from_ball,
           last(over_ball)  as to_ball,
           count(*)         as dots
  pattern (dot{7,})
  define dot as is_dot = 1
) m
join   cr_teams t on t.team_id = m.batting_team_id
order  by m.match_id, m.innings_no;

What comes out of ONE ROW PER MATCH

With ONE ROW PER MATCH (the default), each match becomes one row with only:

1. the PARTITION BY columns, then
2. the MEASURES.

Nothing else. That's why batting_team_id is in the PARTITION BY even though match_id, innings_no already pins down the innings: putting it there is how it gets into the output.

Leave it out and the join fails:

-- doesn't work: batting_team_id isn't in the output of MATCH_RECOGNIZE
select m.match_id, m.innings_no, t.team_code
from   cr_balls_v
match_recognize (
  partition by match_id, innings_no
  order by seq_no
  measures count(*) as dots
  pattern (dot{7,})
  define dot as is_dot = 1
) m
join   cr_teams t on t.team_id = m.batting_team_id;     -- ORA-00904: invalid identifier

There are two fixes: add the column to PARTITION BY when it's the same for the whole partition (as here), or report it as a measure, first(batting_team_id) as batting_team_id.

Pick this when: the spell is a pattern of more than one kind of row, or you also want ALL ROWS PER MATCH later (page 6.3).

Way 2: running count of "not a dot"

with grouped as (
  select b.*,
         sum(case when is_dot = 1 then 0 else 1 end)
           over (partition by match_id, innings_no order by seq_no) as spell
  from   cr_balls_v b
)
select g.match_id,
       g.innings_no,
       t.team_code,
       min(g.over_ball) keep (dense_rank first order by g.seq_no) as from_ball,
       max(g.over_ball) keep (dense_rank last  order by g.seq_no) as to_ball,
       count(*)                                                   as dots
from   grouped g
join   cr_teams t on t.team_id = g.batting_team_id
where  g.is_dot = 1
group  by g.match_id, g.innings_no, t.team_code, g.spell
having count(*) >= 7
order  by g.match_id, g.innings_no;

Way 3: tabibitosan

with numbered as (
  select b.*,
         row_number() over (partition by match_id, innings_no order by seq_no)
       - row_number() over (partition by match_id, innings_no, is_dot order by seq_no) as spell
  from   cr_balls_v b
)
select n.match_id,
       n.innings_no,
       t.team_code,
       min(n.over_ball) keep (dense_rank first order by n.seq_no) as from_ball,
       max(n.over_ball) keep (dense_rank last  order by n.seq_no) as to_ball,
       count(*)                                                   as dots
from   numbered n
join   cr_teams t on t.team_id = n.batting_team_id
where  n.is_dot = 1
group  by n.match_id, n.innings_no, t.team_code, n.spell
having count(*) >= 7
order  by n.match_id, n.innings_no;

This time both row numbers are worked out in the same step, and the second one is partitioned by is_dot as well. The difference is a group number for dots and non-dots alike; we keep only the dots afterwards. It's the same idea as 6.1's Way 3 in one step instead of two.

19c and 26ai

The same on both.

Gotchas

• Only partition columns and measures come out of ONE ROW PER MATCH. Plan your PARTITION BY and MEASURES around what the outer query needs.
• Spells can cross overs. Leeds' 10-dot spell in match 24 runs from 1.5 to 3.2, across three overs and three different bowlers. Grouping by over would break it up.
• "Dot" needs a definition. Ours: nothing scored and a legal ball. A wicket with no runs counts. If your definition left wickets out, you'd get different spells. Say what you mean in the DEFINE.

Try it yourself

For each team, what was its longest dot-ball spell while batting in the 2025 season? Every team should appear, longest first.

select t.team_code, max(m.dots) as longest_drought
from   (select * from cr_balls_v where season = 2025)
match_recognize (
  partition by match_id, innings_no, batting_team_id
  order by seq_no
  measures count(*) as dots
  pattern (dot+)
  define dot as is_dot = 1
) m
join   cr_teams t on t.team_id = m.batting_team_id
group  by t.team_code
order  by longest_drought desc, t.team_code;