Cricket query help

Back to cricket query

Basics ¶

This is an interface to an SQLite database. The database contains a scrape of Cricinfo's Statsguru batting, bowling and team tabs. It allows running (read-only) SQL queries directly, and has some saved queries:

• Bannerwell
• Bowling Bannerwell
• Bannerwell by position
• Bannerwell by year
• Most consecutive wins batting or fielding first
• Fewer runs than innings
• Highest lowest cumulative average
• Biggest difference in home and away batting average
• Biggest difference in home and away bowling average
• Biggest difference in first and second innnings average
• Smallest difference in first and second innnings average
• Integer average before last match
• Least consistent batters
• Lowest high score after N innings
• Median higher than average
• Highest proportion of runs in boundaries
• Most innings outside most common position
• T20I innings with no bowlers bowling out

The scraped data comes from Owen Brasier's cricketstats project. The source for this project is also public.

Other sections on this page are functions, schema, annoyances, result formatting, and latest data.

Functions ¶

In addition to the usual set of SQLite functions (see the links in SQLite's SQL documentation for more information), there is one custom function available: median.

Median ¶

SQLite does not come with a median function, so median is provided as an application-defined function. It is an aggregate function on numeric values (similar to sum). The value will be displayed as described in result formatting, but internally is always a float.

Schema ¶

This is a pretty direct interface to an SQLite database. The database contains a scrape of Cricinfo's Statsguru batting, bowling and team tabs in the following tables, where $gender can be men or women and $format can be test, odi, or t20i.

• $gender_$format_batting_innings
• $gender_$format_bowling_innings
• $gender_$format_team_innings

Table aliases ¶

As the table names are very long, and for many purposes we will want to separate the results by gender and format anyway (comparing a T20I average to a Test average is not often useful), these tables have shorter names available when choosing genders and formats with the checkboxes:

• innings - $gender_$format_batting_innings
• bowling_innings - $gender_$format_bowling_innings
• team_innings - $gender_$format_team_innings

This will allow a separate results table for each gender and format chosen. If we use the full table names (for instance, women_test_bowling_innings) directly, then the results shown for all genders and formats will be the same.

Batting tables ¶

Bowling tables ¶

Team tables ¶

Annoyances ¶

There are several limitations in this database, largely due to a loss of fidelity when scraping and the choice to use a loosely-typed database. These are the most annoying of those.

Boolean columns ¶

Boolean columns (like innings.not_out and team_innings.all_out) are not real booleans, as SQLite doesn't support them. Instead, they contain the strings 'True' and 'False', so compare directly against those values.

Date columns ¶

Similarly, the start_date columns are not real dates. For prettier formatting, they are displayed in a format like this: 2 June 2021.

However, internally they are represented as ISO date strings: 2021-06-02. To find matches on that date, we can use start_date = '2021-06-02'.

Results limit ¶

Hopefully this is less annoying, but I have chosen to limit the number of rows in each table to 100, no matter how many rows were returned. If this becomes a problem I can increase it.

Result formatting ¶

ID columns ¶

Player and match ID columns have special behaviour. A player ID is p followed by digits (with no other characters in the string), and a match ID is the same but with an m prefix.

When a player or match ID is detected (either from the original value, or constructed if you really want to), it will be linked to the relevant Cricinfo player profile or scorecard page.

Other result formatting ¶

In addition to the automatic formatting for date columns and ID columns, numeric columns will be formatted with a thousands separator, and if they contain a non-integer, will be shown to two decimal places.

To force a result column to be displayed literally, prepend an apostrophe - ' - this will be stripped from the output and the rest will be presented verbatim. For example: strftime('%Y', start_date) will display as 2,001, while "'" || strftime('%Y', start_date) will display as 2001.

Latest data ¶

The database is updated daily, although there will be a delay when new teams play their first international match. The latest match for each format is: