The cricket data, and how we made it

The Coastal Cricket League (CCL) is made up. Six teams, two seasons (2025 and 2026), 32 matches, and every one of its 7,551 deliveries. The teams, players and grounds are invented; any resemblance to a real player is a coincidence (and no agent will ring about image rights). What is real is the shape of the data: it behaves like real T20 cricket, so the questions you ask of it are the ones analysts ask of real matches.

The league

select t.team_code, t.team_name, v.venue_name
from   cr_teams t
join   cr_venues v on v.venue_id = t.home_venue_id
order  by t.team_id;

Each season, every team plays every other team once (15 league matches, at the home team's ground), then the top two meet in a final. Winning is worth 2 points, a tie 1.

select m.season,
       w.team_name as champion,
       l.team_name as runner_up,
       m.win_type,
       m.win_margin
from   cr_matches m
join   cr_teams w on w.team_id = m.winner_id
join   cr_teams l on l.team_id = case when m.winner_id = m.home_team_id
                                      then m.away_team_id
                                      else m.home_team_id end
where  m.stage = 'FINAL'
order  by m.season;

The tables

CR_VENUES 1──< CR_TEAMS 1──< CR_PLAYERS
    │              │              │
    └────< CR_MATCHES >───────────┘ (player of the match)
                  │
                  1
                  │
                  ^
           CR_DELIVERIES  (batter, non-striker, bowler, player out, fielder → CR_PLAYERS)

CR_MATCHES, one row per match:

CR_DELIVERIES, one row per ball. The key is (MATCH_ID, INNINGS_NO, SEQ_NO):

CR_BALLS_V is CR_DELIVERIES joined to CR_MATCHES (for SEASON, MATCH_NO, MATCH_DATE), plus columns most questions need:

The 1/0 columns are numbers on purpose, so you can add them up: sum(is_six) is the number of sixes. (26ai has a real BOOLEAN type; Part 10 shows it. The dataset sticks to what runs on 19c.)

A quick feel for it, the top run-scorers of 2025:

select p.player_name,
       t.team_code,
       sum(b.runs_batter)                                            as runs,
       sum(case when b.extra_type = 'WD' then 0 else 1 end)           as balls,
       round(100 * sum(b.runs_batter)
                 / sum(case when b.extra_type = 'WD' then 0 else 1 end), 1) as strike_rate
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
join   cr_teams   t on t.team_id   = p.team_id
where  b.season = 2025
group  by p.player_name, t.team_code
order  by runs desc
fetch  first 5 rows only;

Balls faced leave out wides, because a wide isn't counted as a ball the batter faced. Notice case when b.extra_type = 'WD' then 0 else 1 end: when EXTRA_TYPE is null, null = 'WD' is not true, so the ELSE branch counts the ball. That's NULL logic working for us, and Part 1 covers it properly.

How we made the data

The data comes from a small cricket simulator (tools/make_cricket.py in the course repo), run once with a fixed random seed. The same seed always gives the same matches, ball for ball, so every expected result in this guide stays true for everyone.

What the simulator does, ball by ball:

1. Picks the bowler for each over from the fielding side's five best bowlers: at most 4 overs each, never two overs in a row, fast bowlers favored in the powerplay and at the death, spinners in the middle overs.
2. Decides if it's a wide or a no-ball (about 3% and 0.7% of deliveries). Those are bowled again, so the over gets an extra delivery.
3. Decides the outcome of a legal ball: a dot, 1, 2, 3, 4, 6 or a wicket. The chances depend on the phase (more fours in the powerplay, more sixes and more wickets at the death) and on skill ratings: an opener scores more and gets out less than a tail-ender, and a strike bowler concedes less than a part-timer.
4. Picks how a wicket happened: mostly caught, then bowled, lbw, run out, and stumped (only off spinners). A catch goes to a fielder, sometimes the keeper, sometimes the bowler ("caught and bowled"). A run out can dismiss the non-striker.
5. Rotates the strike on odd runs and at the end of each over, and sends in the next batter after a wicket.
6. Ends the innings after 20 overs or 10 wickets, and ends the chase the moment the target is reached.

Then it works out the result, the points table, which two teams reach the final, and the player of the match (the best all-round contribution on the winning side).

The numbers come out like real T20: first innings average about 162, the highest score is 207, there are two centuries, and teams are bowled out now and then.

Planted on purpose

A few patterns are rare in real cricket but too good to leave out of a SQL guide, so the simulator forces them at fixed places. Everything else is left to chance.

What the simulator leaves out

Real cricket has more than our data does: rain-shortened matches, super overs, free hits after no-balls, rarer dismissals (hit wicket, obstructing the field), retired batters and substitutes. None of that changes how the SQL works, and leaving it out keeps the data easy to reason about.

Try it yourself

How many matches did each team win in 2025? (Hint: one team didn't win any. Make sure it still shows up with 0. And the wins only add up to 15 of the 16 matches: one was a tie.)

select t.team_name, count(m.match_id) as wins
from   cr_teams t
left   join cr_matches m on m.winner_id = t.team_id and m.season = 2025
group  by t.team_name
order  by wins desc, t.team_name;