Three or more sixes in a row

The first real pattern: streaks. A streak is "the same kind of row, several times in a row", and it's the most common sequence question there is (login streaks, winning streaks, days above a temperature). Here it's sixes.

The test case

Find every time a batter hit sixes off three or more balls in a row that they faced. Show the batter, the match, the first and last ball of the streak, and how many sixes.

"Balls they faced" matters twice:

• A wide isn't a ball the batter faced, so wides don't count (and don't break a streak).
• The other batter's balls don't break a streak either. If our batter hits a six, takes a single and then hits two more sixes after the other batter has faced a ball, that's three sixes in a row for him.

Expected result

Karthik Raman clearly skipped the coaching session on building an innings.

Look at Callum Reid: 17.6 to 18.4 crosses from one over into the next. He hit the last ball of over 18 for six, the strike changed at the end of the over, and the next two balls he faced were sixes too. Any answer that groups by over gets this one wrong.

Way 1: MATCH_RECOGNIZE

select p.player_name, m.match_id, m.from_ball, m.to_ball, m.sixes
from   (select *
        from   cr_balls_v
        where  extra_type is null or extra_type <> 'WD')    -- balls the batter faced
match_recognize (
  partition by match_id, batter_id      -- each batter's innings on its own
  order by seq_no
  measures first(over_ball) as from_ball,
           last(over_ball)  as to_ball,
           count(*)         as sixes
  pattern (six{3,})
  define six as runs_batter = 6
) m
join   cr_players p on p.player_id = m.batter_id
order  by m.match_id, m.batter_id;

What each part does:

• PARTITION BY match_id, batter_id runs the pattern separately for every batter in every match. Inside one partition the rows are only that batter's balls, so the other batter's balls can't break the streak. (A batter bats in only one innings per match, so we don't need innings_no here.)
• ORDER BY seq_no reads those balls in the order they were bowled.
• DEFINE six AS runs_batter = 6: a row is a six when the batter hit a six off it.
• PATTERN (six{3,}): three or more six rows, one after another.
• MEASURES: FIRST(over_ball) and LAST(over_ball) are the first and last rows of the match, COUNT(*) the number of rows in it.

The output has the partition columns (MATCH_ID, BATTER_ID) plus the measures, which is why we can join CR_PLAYERS on m.batter_id afterwards.

Pick this when: the question is "a sequence of rows like this". It reads the way you'd say it.

Way 2: running count of "not a six" (the reset trick)

Keep a running count of balls that were not sixes. It stays the same during a streak of sixes, so it works as a group number for the streak.

with faced as (
  select b.*,
         sum(case when runs_batter = 6 then 0 else 1 end)
           over (partition by match_id, batter_id order by seq_no) as streak_grp
  from   cr_balls_v b
  where  extra_type is null or extra_type <> 'WD'
)
select p.player_name,
       f.match_id,
       min(f.over_ball) keep (dense_rank first order by f.seq_no) as from_ball,
       max(f.over_ball) keep (dense_rank last  order by f.seq_no) as to_ball,
       count(*)                                                   as sixes
from   faced f
join   cr_players p on p.player_id = f.batter_id
where  f.runs_batter = 6
group  by p.player_name, f.match_id, f.batter_id, f.streak_grp
having count(*) >= 3
order  by f.match_id, f.batter_id;

Two things to notice:

• MIN(...) KEEP (DENSE_RANK FIRST ORDER BY seq_no) means "the over_ball of the row with the lowest seq_no". Plain MIN(over_ball) would compare the text '9.1' with '10.2' and pick the wrong one.
• HAVING COUNT(*) >= 3 does the job of {3,}.

Pick this when: you're on a database without MATCH_RECOGNIZE, or the streak is the only pattern you need. It's the classic "gaps and islands" answer (Part 13).

Way 3: row-number difference (tabibitosan)

Number all of a batter's balls, and separately number just the sixes. Within a streak both numbers go up by one each time, so their difference stays the same. The Japanese name tabibitosan ("travelers") is what this trick is called in the Oracle community.

with faced as (
  select b.*,
         row_number() over (partition by match_id, batter_id order by seq_no) as n_all
  from   cr_balls_v b
  where  extra_type is null or extra_type <> 'WD'
),
sixes as (
  select f.*,
         n_all - row_number() over (partition by match_id, batter_id order by seq_no) as streak_grp
  from   faced f
  where  runs_batter = 6
)
select p.player_name,
       s.match_id,
       min(s.over_ball) keep (dense_rank first order by s.seq_no) as from_ball,
       max(s.over_ball) keep (dense_rank last  order by s.seq_no) as to_ball,
       count(*)                                                   as sixes
from   sixes s
join   cr_players p on p.player_id = s.batter_id
group  by p.player_name, s.match_id, s.batter_id, s.streak_grp
having count(*) >= 3
order  by s.match_id, s.batter_id;

The WHERE runs_batter = 6 has to sit in the second step, after n_all is worked out. Filter first and every remaining row looks consecutive.

Pick this when: same as Way 2. Some people find one easier to read, some the other.

19c and 26ai

All three ways run unchanged on both.

Gotchas

• Order by something unique. seq_no is unique within an innings. If you ordered by over_no alone, the balls inside an over could come in any order, and the streaks you find could change from one run to the next.
• Filter before the pattern, not after. Leaving wides in would make a wide break a streak; filtering them out after MATCH_RECOGNIZE would be too late.
• OVER_BALL is text. Sort and compare by seq_no, show over_ball.

Try it yourself

Change the question to fours: three or more fours in a row off balls the batter faced, in the 2026 season only. How many streaks are there, and what's the longest?

select count(*) as streaks, max(fours) as longest
from   (select *
        from   cr_balls_v
        where  (extra_type is null or extra_type <> 'WD')
        and    season = 2026)
match_recognize (
  partition by match_id, batter_id
  order by seq_no
  measures count(*) as fours
  pattern (four{3,})
  define four as runs_batter = 4
);