Built and run by one person.
For Analysts Using the Tigzig Cricket Database: 14 Things That Trip People Up
Top•DATS-4 Database AI Suite•BRIQ•DUCKIT - CSV to DuckDB

For Analysts Using the Tigzig Cricket Database: 14 Things That Trip People Up

Published: September 11, 2026

Related toolsDATS-4 Database AI Suite•BRIQ•DUCKIT - CSV to DuckDB

ODI, Test, T20 and IPL, since 2001. 11K+ matches, 5M+ deliveries, refreshed twice a day. Postgres and DuckDB, open to SQL, with the full data download.

  1. winner_innings is a yes or no flag. It means the match was won by an innings, which can only happen in a Test. It is not the innings the winner batted in, so filtering it to 2 returns nothing every time. For batting order use the margin instead: winner_runs means the side batting first won, winner_wickets means the side batting second won. In this data, of 918 Tests, 739 produced a result and 170 of those were won by an innings.

  2. team1 and team2 are the order the two sides are listed in, not who batted first. In T20s the side listed second did bat second about nine times in ten. So if you use team2 to mean the chasing side, your answer is wrong for roughly one match in ten. Take batting order from the margin columns above, or from the toss.

  3. Not every wicket is the bowler's. There are run outs, retired hurt and retired out, and the bowler's name still sits on that row because they bowled the ball. So counting every row where wicket_type is not null gives them wickets they never took, and nearly one bowler performance in seven comes out too high. Look at what wicket_type holds and keep only what you want. Run outs are most of it.

  4. An over is not six rows, and over_no starts at zero. Extras add deliveries inside an over, so no amount of multiplying by six gives you a ball count, and the longest over in this data runs to twenty one deliveries.

  5. The extras columns are blank, not zero. Cricsheet leaves wides, no balls, byes, leg byes and penalty blank when they do not apply, while runs_off_bat and extras carry a real zero. So wides = 0 matches no rows in the whole table. Wrap them in coalesce.

  6. extras is a total, and wides, no balls, byes, leg byes and penalty are the breakdown of it. Every row adds up exactly.

  7. toss_decision has only two values, 'bat' and 'field'. There is no 'bowl' in this data.

  8. Team names carry no gender. The women's side is stored as Australia, exactly like the men's. Filter on the gender column and use the plain country name.

  9. The wides column holds runs, not a count. A plain wide is 1, but a wide that runs away to the boundary is 5. So counting wides = 1 misses about one wide in ten, and summing the column gives you runs rather than the number of wides. To count them, use wides is not null. There is no separate column holding the count.

Five more

  1. target_runs already includes the extra run needed to win. It is the first innings total plus one, so do not add one yourself.

  2. Do not hardcode where the death overs start. The over number differs by format, and on a rain shortened innings a fixed threshold can return nothing at all, which reads as "there were no death overs" rather than as a filter that missed.

  3. Players are stored as name strings, so joining to another source on a name is fragile. The people and match_players tables carry stable identifiers and cross references to other cricket sites, and they are the safer join.

  4. The two engines are not identical. Postgres and DuckDB differ on some SQL, so a query that refuses on one can run on the other. Worth trying before you rewrite it.

  5. One that is not about the data at all, and no amount of checking the data will surface it. On the GET endpoint a plus sign in your SQL arrives as a space, because that is what a plus means in a web address. SUM(runs_off_bat + extras) becomes SUM(runs_off_bat extras). Sometimes that is an error, and sometimes it is valid SQL that returns a number under the wrong heading. Write the plus as %2B, or use POST, where it does not arise.

Where to go next

What each column means is Cricsheet's definition, and their format page is the source of truth for the fields: cricsheet.org/format/csv_ashwin

The Tigzig cricket database is free to query with SQL or download in full, no signup, no API key: tigzig.com/apis/database

The Tigzig cricket database API - ODI, Test, T20 and IPL, open to SQL with a full data download

Compliance note

Data source is Cricsheet (cricsheet.org), licensed ODC-BY 1.0. TigZig is not affiliated with or endorsed by Cricsheet.

Working on something similar? How I work covers the rates, the availability and what I take on.