Practice: three pattern problems

select m.match_id, m.innings_no, t.team_code, m.from_ball, m.to_ball, m.deliveries
from   (select * from cr_balls_v where season = 2026)
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 deliveries
  pattern (scoring+)
  define scoring as is_dot = 0
) m
join   cr_teams t on t.team_id = m.batting_team_id
order  by m.deliveries desc, m.match_id
fetch  first 3 rows only;

with grouped as (
  select b.*,
         sum(is_dot) over (partition by match_id, innings_no order by seq_no) as streak
  from   cr_balls_v b
  where  season = 2026
)
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 deliveries
from   grouped g
join   cr_teams t on t.team_id = g.batting_team_id
where  g.is_dot = 0
group  by g.match_id, g.innings_no, t.team_code, g.streak
order  by deliveries desc, g.match_id
fetch  first 3 rows only;

The reset trick needs no CASE this time: IS_DOT is already 1 for the rows that end a streak, so a running SUM(is_dot) is the streak number.

select p.player_name as bowler, count(*) as setups
from   (select * from cr_balls_v where is_legal = 1)
match_recognize (
  partition by match_id, bowler_id
  order by innings_no, seq_no
  measures match_number() as mno
  pattern (dot{3} w)
  define dot as is_dot = 1 and is_wicket = 0,
         w   as is_bowler_wicket = 1
) m
join   cr_players p on p.player_id = m.bowler_id
group  by p.player_name
having count(*) >= 2
order  by setups desc, p.player_name;

with legal as (
  select b.*,
         case when is_dot = 1 and is_wicket = 0 then 1 else 0 end as quiet
  from   cr_balls_v b
  where  is_legal = 1
),
looked_back as (
  select l.*,
         lag(quiet, 1) over (partition by match_id, bowler_id order by innings_no, seq_no) as q1,
         lag(quiet, 2) over (partition by match_id, bowler_id order by innings_no, seq_no) as q2,
         lag(quiet, 3) over (partition by match_id, bowler_id order by innings_no, seq_no) as q3
  from   legal l
)
select p.player_name as bowler, count(*) as setups
from   looked_back x
join   cr_players p on p.player_id = x.bowler_id
where  x.is_bowler_wicket = 1 and x.q1 = 1 and x.q2 = 1 and x.q3 = 1
group  by p.player_name
having count(*) >= 2
order  by setups desc, p.player_name;

dot{3} w doesn't care whether there were more than three dots before the wicket: with five dots, the pattern matches the last three plus the wicket. The LAG version checks only the three balls right before the wicket, so the two agree.

with results as (
  select match_id, match_date, home_team_id as team_id,
         case when winner_id = home_team_id then 'W'
              when winner_id is null         then 'T'
              else 'L' end as result
  from   cr_matches
  union all
  select match_id, match_date, away_team_id,
         case when winner_id = away_team_id then 'W'
              when winner_id is null         then 'T'
              else 'L' end
  from   cr_matches
)
select t.team_name, m.first_win, m.last_win, m.wins
from   results
match_recognize (
  partition by team_id
  order by match_date
  measures first(match_date) as first_win,
           last(match_date)  as last_win,
           count(*)          as wins
  pattern (win{3,})
  define win as result = 'W'
) m
join   cr_teams t on t.team_id = m.team_id
order  by m.wins desc, m.first_win;

with results as (
  select match_id, match_date, home_team_id as team_id,
         case when winner_id = home_team_id then 'W'
              when winner_id is null         then 'T'
              else 'L' end as result
  from   cr_matches
  union all
  select match_id, match_date, away_team_id,
         case when winner_id = away_team_id then 'W'
              when winner_id is null         then 'T'
              else 'L' end
  from   cr_matches
),
grouped as (
  select r.*,
         sum(case when result = 'W' then 0 else 1 end)
           over (partition by team_id order by match_date) as streak
  from   results r
)
select t.team_name,
       min(g.match_date) as first_win,
       max(g.match_date) as last_win,
       count(*)          as wins
from   grouped g
join   cr_teams t on t.team_id = g.team_id
where  g.result = 'W'
group  by t.team_name, g.streak
having count(*) >= 3
order  by wins desc, first_win;

Chennai's 4-match streak starts with the 2025 final and carries on into 2026: partitioning by team (not by team and season) is what lets it cross the season break. Unpivoting the two team columns into one with UNION ALL is a step you'll use all the time; Part 7 shows UNPIVOT, which does it in one clause.