IPL Analytics with PostgreSQL

A ball-by-ball analysis of the Indian Premier League in pure PostgreSQL: 25 analytical queries, 2 views, 2 PL/pgSQL functions and a set of indexes, built on two tables and every delivery bowled since 2008. It answers questions such as who won each season's Orange and Purple Cap, who has the best batting average, and what the highest successful chase is.

The whole pipeline runs with one command and is tested in CI, so you can reproduce every result.

Highlights (from the full IPL data)

Question	Answer
Highest successful chase	262, Punjab Kings v Kolkata Knight Riders (2024)
Best individual innings in the list	175, CH Gayle (2013)
Most runs in a season (Orange Cap)	733, CH Gayle (2012) and MEK Hussey (2013)
Most wickets in a season (Purple Cap)	32, DJ Bravo (2013)
Best batting average (min 500 runs)	49.24, B Sai Sudharsan
Best economy (min 30 overs)	6.19, Sohail Tanvir

More in docs/results.md, with the pgAdmin screenshots.

Quick start

You need PostgreSQL 13 or newer with psql on your PATH.

git clone https://github.com/hemanthreddy2105/IPL-Analytics-Postgresql.git
cd IPL-Analytics-Postgresql

# 1. Try it on the bundled synthetic sample (takes seconds, no download needed)
./scripts/run_all.sh                 # Windows: .\scripts\run_all.ps1

# 2. Run it on the real IPL data
#    put matches.csv and deliveries.csv in data/raw/ (see data/README.md), then
./scripts/run_all.sh --real          # Windows: .\scripts\run_all.ps1 -Real

Connection settings come from the usual PGHOST, PGUSER, PGPASSWORD variables. Query output is written to results/run_output.txt. The sample data is synthetic (fictional teams and players); it exists to prove the pipeline works, so use the real data for actual cricket results.

Prefer pgAdmin? Run the files in sql/ in numeric order. File 00 must be run on the postgres database; connect to ipl_analytics for the rest.

Power BI dashboards

The same data is also explored in a five-page Power BI report (executive, batting, bowling, team performance and venue analysis). See powerbi/.

What is inside

sql/
  00_create_database.sql      create the database (safe to re-run)
  01_schema.sql               matches + deliveries tables, primary and foreign keys
  02_load_data.sql            load the CSV files with \copy
  03_clean_and_validate.sql   NA -> NULL, row counts, integrity check
  04_basic_queries.sql        queries 1-17
  05_advanced_queries.sql     queries 18-25 (CTEs, window functions)
  06_views.sql                vw_batting_stats, vw_bowling_stats
  07_functions.sql            get_player_runs(), get_player_strike_rate()
  08_indexes.sql              7 indexes, ANALYZE, EXPLAIN ANALYZE demo
scripts/                      run_all.sh / run_all.ps1, sample-data generator
data/                         sample/ (synthetic, committed) and raw/ (your real CSVs, git-ignored)
docs/                         schema + ER diagram, cricket rules, query catalogue, results
screenshots/                  pgAdmin output for queries 18-25
powerbi/                      Power BI report (.pbix), page screenshots and notes
results/                      reference output on the sample data
.github/workflows/ci.yml      runs the full pipeline on every push

SQL concepts demonstrated

Concept	Where
Joins, grouping, HAVING, FILTER aggregates	queries 1-17
CTEs (WITH) and multi-step logic	queries 18-25
Window functions: RANK, DENSE_RANK	queries 18-20, 24, 25
Tie handling: FETCH FIRST ... WITH TIES	queries 3, 5
Safe maths: NULLIF, COALESCE, NULLS LAST	queries 15, 16, 21, 23
Views	06_views.sql
PL/pgSQL functions	07_functions.sql
Indexing and query plans	08_indexes.sql
Idempotent scripts (re-runnable)	every file

The full list with the question each query answers is in docs/query_catalog.md.

Getting the cricket maths right

Counting the wrong deliveries gives plausible but wrong numbers. This project follows the official conventions, documented in docs/cricket_rules.md:

• wides are not balls faced; wides and no-balls are not legal balls;
• byes and leg-byes are not charged to the bowler;
• run-outs and retirements are not bowler wickets;
• a batting average counts a non-striker run-out as an out and ignores "retired hurt";
• the chasing side is read from the second innings, not assumed to be team2;
• rate statistics have minimum-sample thresholds (500 balls, 500 runs, 30 overs).

Data

The real dataset is a Kaggle ball-by-ball IPL dump (matches.csv, deliveries.csv). It is not committed; data/README.md lists the expected columns and the load steps and troubleshooting. The bundled data/sample/ files come from scripts/generate_sample_data.py.

Author

Hemanth Reddy, B.Sc. Economics (Data Science), Shiv Nadar University Chennai.

License

MIT