A production-grade PostgreSQL database system for analysing and comparing athlete performance across Cricket and Football. Built entirely in SQL — no ORM, no abstraction layers — with a focus on schema design, query intelligence, and database-level business logic enforcement.
Sports franchises, scouts, and analysts make million-dollar decisions about players using fragmented, sport-specific tools. There is no unified system that can answer: "Who is the most impactful athlete across our entire portfolio — cricket or football?"
AthleteIQ solves this by building a sport-agnostic data model that works for any sport, while preserving the depth needed for sport-specific analysis.
AthleteIQ is structured in two layers:
UNIVERSAL LAYER
├── sports → root reference: Cricket, Football
├── athletes → every player, any sport
├── teams → IPL franchises, PL clubs, national sides
├── competitions → IPL, Premier League, T20 World Cup, etc.
├── matches → every match with scores and outcomes
└── performances → bridge: who played, in which match, in what role
SPORT-SPECIFIC LAYER
├── cricket_batting_stats → batting metrics + auto-computed strike rate
├── cricket_bowling_stats → bowling metrics + auto-computed economy & average
└── football_player_stats → football metrics + auto-computed pass accuracy + xG/xA
9 tables. 2 layers. Every relationship enforced by foreign keys.
The core design challenge: Cricket and Football have completely different performance metrics. The solution is a Supertype-Subtype pattern — a shared universal core (supertype) extended by sport-specific tables (subtypes). This enables cross-sport queries without NULL pollution or data duplication.
Generated Columns for derived metrics
Strike rate, economy rate, bowling average, and pass accuracy are GENERATED ALWAYS AS ... STORED columns. They are computed automatically from raw inputs — you cannot insert an incorrect strike rate. This enforces data consistency at the schema level.
Composite Unique Constraint on performances
UNIQUE(match_id, athlete_id, performance_role) allows an all-rounder like Hardik Pandya to have two rows in a match (one as batsman, one as bowler) while preventing duplicate entries for the same role.
Nullable bowling_average (not 0)
A bowler with 0 wickets has no bowling average — not an average of 0. NULL is used intentionally to represent "not applicable." This distinction matters for aggregations: AVG() ignores NULLs but includes 0s.
Soft deletion via is_active Athletes and teams are never hard-deleted. Historical data is the most valuable data in an analytics system.
State machine for match_status
'Scheduled' → 'Live' → 'Completed' — every match transitions through defined stages with a default of 'Scheduled'.
| Entity | Volume |
|---|---|
| Sports | 2 (Cricket, Football) |
| Teams | 47 (IPL franchises, international cricket, Premier League, La Liga, Bundesliga, Serie A, national sides) |
| Athletes | 72 (real players across 15+ nationalities) |
| Competitions | 15 (IPL 2023/24, T20 WC 2022/24, CWC 2023, PL 2022-24, UCL, Euro 2024, La Liga, Bundesliga) |
| Matches | 78 |
| Performances | 73 |
| Batting stat rows | 30 |
| Bowling stat rows | 12 |
| Football stat rows | 34 |
Section 1 — Cricket Performance
- Q01: Top batsmen by strike rate
- Q02: Best bowlers by economy rate and dot ball %
- Q03: All-rounder impact index (custom composite score)
- Q04: IPL 2024 batting leaderboard with RANK()
- Q05: Cumulative runs + rolling 3-match average
- Q06: Form analysis — last 3 matches vs career average
Section 2 — Football Performance
- Q07: Goals + assists per 90 minutes
- Q08: xG overperformance — clinical finishers vs lucky ones
- Q09: Defensive actions per game
- Q10: Premier League top scorers leaderboard with RANK()
Section 3 — Cross-Sport Intelligence
- Q11: Nationality breakdown — which nations produce the most players
- Q12: Role distribution across both sports
- Q13: Competition scoring summary — highest-scoring tournaments
Section 4 — Team & Match Analysis
- Q14: IPL team batting strength comparison
- Q15: Head-to-head records between any two teams
- Q16: Win rate by team across all competitions
Section 5 — Advanced SQL Showcase
- Q17: LAG — match-by-match score progression with delta
- Q18: DENSE_RANK — global athlete ranking by appearances
SQL techniques used: Window functions (RANK, DENSE_RANK, ROW_NUMBER, LAG, SUM OVER, AVG OVER), CTEs, Composite aggregations, FILTER clause, NULLIF, type casting, CASE expressions, STRING_AGG, JOIN chains across 5 tables, HAVING, computed ratios.
Trigger: Cross-Sport Integrity
trg_check_sport_integrity fires before every INSERT or UPDATE on performances. It verifies that the athlete's sport matches the match's sport via the competition. A cricket player cannot be inserted into a football match — the database rejects it with a descriptive error.
-- This raises an exception automatically:
INSERT INTO performances (match_id, athlete_id, ...)
VALUES (football_match_id, cricket_athlete_id, ...);
-- ERROR: Integrity Error: Athlete 1 (sport_id=1) cannot participate in Match 44 (sport_id=2)Function: get_team_stats(team_name) Returns win/loss/draw summary for any team across competitions.
SELECT * FROM get_team_stats('Mumbai Indians');
SELECT * FROM get_team_stats('Arsenal');Function: get_player_summary(player_name) Returns performance summary for any athlete — automatically detects sport and returns relevant metrics.
SELECT * FROM get_player_summary('Virat Kohli');
SELECT * FROM get_player_summary('Erling Haaland');04_indexes.sql contains 9 indexes targeting the most common query patterns:
-- Foreign key indexes for fast JOINs
CREATE INDEX idx_perf_athlete_id ON performances(athlete_id);
CREATE INDEX idx_perf_match_id ON performances(match_id);
-- Time-series index for window functions
CREATE INDEX idx_matches_date ON matches(match_date DESC);
-- Stat-specific indexes for leaderboard queries
CREATE INDEX idx_fb_goals_assists ON football_player_stats(goals DESC, assists DESC);
CREATE INDEX idx_crick_runs ON cricket_batting_stats(runs_scored DESC);Prerequisites: PostgreSQL 14+ (built on PostgreSQL 18)
# 1. Create the database
psql -U postgres -c "CREATE DATABASE athleteiq;"
# 2. Run schema
psql -U postgres -d athleteiq -f 01_schema.sql
# 3. Load data
psql -U postgres -d athleteiq -f 02_data.sql
# 4. Create indexes
psql -U postgres -d athleteiq -f 04_indexes.sql
# 5. Create triggers and functions
psql -U postgres -d athleteiq -f 05_procedures.sql
# 6. Run any query from the library
psql -U postgres -d athleteiq -f 03_queries.sqlAthleteIQ/
├── 01_schema.sql → All CREATE TABLE statements (9 tables)
├── 02_data.sql → Sample data (72 athletes, 47 teams, 78 matches)
├── 03_queries.sql → Query library (18 analytical queries)
├── 04_indexes.sql → Performance optimization indexes
├── 05_procedures.sql → Triggers and stored functions
└── README.md → This file
Player Valuation Engine A scoring system that computes form, consistency, and impact scores per player — stored back into PostgreSQL as queryable data. Includes feature-level breakdown so a scout can query why a player is scored highly, not just that they are.
Performance Prediction Layer Forecast player metrics for upcoming competitions using historical data. Store predictions alongside actual values to measure model accuracy over time using Mean Absolute Error calculated directly in SQL.
Squad Optimization Given a budget constraint, select the highest-scoring squad within role quotas. A parameterised PostgreSQL function that takes budget and sport as inputs and returns the optimal selection.
C++ ETL Ingestion Layer A high-performance C++ pipeline using libpqxx to ingest raw CSV match data, apply validation and transformation logic, and bulk load into the AthleteIQ schema — replacing manual SQL inserts with an automated, repeatable ingestion process.
| Layer | Technology |
|---|---|
| Database | PostgreSQL 18 |
| Query interface | pgAdmin 4 |
| Version control | Git / GitHub |