IPL Analytics Dashboard — Capstone Project

This capstone brings together every skill from the course into one substantial deliverable: a complete SQL analytics layer for an IPL season, the kind of query suite that powers a real cricket dashboard. You will design a normalised schema, load it, and build a comprehensive set of reporting queries and reusable views spanning filtering, aggregation, joins, subqueries, CTEs, and window functions.

The goal is a portfolio-grade artifact that demonstrates genuine command of SQL, not a toy. Your analytics layer will answer the questions a cricket analyst actually asks, team standings, player leaderboards, venue trends, per-team rankings, running scorelines, and form comparisons, all as clean, documented, reusable SQL. This is the deliverable you can show an employer as proof of practical database skill.

You will work in phases, building from a solid schema to core reporting queries to advanced analytical views, and finally packaging it with documentation and a rubric self-assessment. Treat this as a real project: design deliberately, verify each query against expected results, and aim for SQL that another engineer could read, trust, and extend without having to ask you what it does.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the analytics team that builds the entire statistical backbone of a televised IPL season, every graphic, every standings table, every trend line, from one well-designed data system. Just as they architect the data model, compile the core stats, then layer on the advanced insights that make the broadcast shine, you build schema, core queries, then analytical views. Just as their work must be reliable enough to air to millions, your queries must be correct and reusable. Just as the broadcast team cannot bolt graphics onto a flawed data model without everything wobbling, each capstone layer depends on the one beneath, so schema errors surface as query bugs and query bugs as wrong dashboards. Just as the season's coverage reuses the same backbone match after match, your views are written once and queried everywhere. This reveals why the capstone mirrors the real craft of turning raw cricket data into a complete analytics product.

Learning Objectives

Design a normalised, fully constrained multi-table schema that models an IPL season and enforces data integrity through keys, types, and CHECK constraints.
Compose core reporting queries using filtering, aggregation, grouping, and multi-table joins to produce standings, leaderboards, and venue breakdowns.
Apply subqueries, CTEs, and window functions to build advanced analytics like per-team rankings, running scorelines, and form comparisons.
Package complex logic into reusable, documented views so the analytics layer can be queried simply and extended by others over time.
Verify every query against expected results and reason about performance, indexing join and filter keys to keep the dashboard responsive at scale.

Technical Requirements

Use PostgreSQL with the psql client, organising the project into a schema file, a seed file, and an analytics file of queries and views, all rerunnable.
Model at least five related tables (teams, venues, players, matches, innings or deliveries) with primary keys, foreign keys, and appropriate CHECK constraints.
Seed the schema with realistic, valid data spanning multiple teams, venues, and matches so every analytical query produces meaningful, verifiable output.
Implement core queries: a team standings table, a player run leaderboard, and a per-venue scoring summary, each ordered deterministically with tie-breakers.
Implement advanced analytics: top-N scorer per team via a window function, a running cumulative scoreline, and a form comparison using LAG.
Encapsulate at least two reporting outputs as CREATE VIEW definitions, with comments documenting each view's purpose and the columns it returns.
Index foreign keys and frequently filtered columns, and confirm with EXPLAIN that at least one heavy query uses an index rather than a full scan.

Architecture & Design

Structure the project as three rerunnable scripts that mirror professional practice: schema.sql defines the normalised tables and constraints, seed.sql loads valid sample data in dependency order, and analytics.sql holds the reporting queries and view definitions. This separation keeps structure, data, and logic independently maintainable, and lets anyone rebuild the entire analytics layer from scratch with three commands.

The data model centres on a deliveries or innings fact table referencing dimension tables for players, teams, venues, and matches, the classic star-like shape that makes analytical queries clean. Each fact references its dimensions by foreign key, so joins reassemble full context, while aggregates and window functions compute standings and trends over the fact rows partitioned by the dimensions that matter.

Design your views as the public interface to the analytics layer. A team_standings view and a player_leaderboard view encapsulate the join, aggregation, and ordering logic so consumers simply SELECT from them, exactly as a dashboard would. Index the foreign keys and the columns your heaviest queries filter or rank on, and verify with EXPLAIN that the analytical workload stays efficient as the season's data grows.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the analytics team's well-organised operation: a fixed data model, a reliable feed of match data, and a library of prepared graphics anyone on the team can pull up. Just as separating the data model, the feed, and the graphics keeps the operation maintainable, your three scripts separate schema, seed, and analytics. Just as the prepared graphics hide the messy computation behind a clean on-screen result, your views hide query complexity behind a simple SELECT. Just as a new analyst joining mid-season can operate the graphics library without relearning the data model, a consumer of your views needs no knowledge of the joins and windows inside them. Just as upgrading one graphic never requires rebuilding the feed, improving a view's internals changes nothing for the dashboards that select from it. This reveals why a layered, view-driven architecture turns raw data into a usable analytics product.

sql

-- ARCHITECTURE SKELETON (fill in across three files).

-- ============ schema.sql ============
-- DROP TABLE IF EXISTS deliveries, innings, matches, players, venues, teams CASCADE;
CREATE TABLE teams   (team_id INT PRIMARY KEY, team_name TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE venues  (venue_id INT PRIMARY KEY, venue_name TEXT NOT NULL UNIQUE);
CREATE TABLE players (
    player_id INT PRIMARY KEY, player_name TEXT NOT NULL,
    team_id INT NOT NULL REFERENCES teams(team_id)
);
CREATE TABLE matches (
    match_id INT PRIMARY KEY, venue_id INT NOT NULL REFERENCES venues(venue_id),
    home_team INT NOT NULL REFERENCES teams(team_id),
    away_team INT NOT NULL REFERENCES teams(team_id),
    match_date DATE NOT NULL, CHECK (home_team <> away_team)
);
CREATE TABLE deliveries (
    delivery_id INT PRIMARY KEY,
    match_id   INT NOT NULL REFERENCES matches(match_id),
    batsman_id INT NOT NULL REFERENCES players(player_id),
    over_no    INT NOT NULL CHECK (over_no BETWEEN 1 AND 20),
    ball_no    INT NOT NULL CHECK (ball_no BETWEEN 1 AND 6),
    runs       INT NOT NULL CHECK (runs BETWEEN 0 AND 6),
    is_wicket  BOOLEAN NOT NULL DEFAULT FALSE
);

-- Index foreign keys and hot filter/rank columns.
CREATE INDEX idx_deliveries_match  ON deliveries (match_id);
CREATE INDEX idx_deliveries_bat    ON deliveries (batsman_id);
CREATE INDEX idx_players_team      ON players (team_id);

-- ============ seed.sql ============  (load parents before children)
-- INSERT INTO teams ...; INSERT INTO venues ...; INSERT INTO players ...;
-- INSERT INTO matches ...; INSERT INTO deliveries ...;

-- ============ analytics.sql ============  (queries + views, built in phases)

Phase 1 — Core Foundation

In Phase 1 you build and seed the schema, then write the core reporting queries that form the dashboard's backbone. Create the team standings, the player run leaderboard, and the per-venue scoring summary, each using joins and aggregation with deterministic ordering. Verify each against hand-computed expected results so you trust the foundation before layering advanced analytics on top.

These core queries exercise Modules 1 through 3 together: filtering, aggregation, grouping, and multi-table joins. Get the ordering and tie-breakers right, and confirm the totals reconcile with the seed data. This phase establishes that your schema is sound and your fundamental reporting is correct, the essential base every later analytical view depends upon.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the analytics team first nailing the basic scoreboard, standings, leading run scorers, ground records, before any fancy graphics. Just as a broadcast cannot show clever trends if the basic totals are wrong, your advanced views cannot be trusted if the core queries are off. Just as they double-check the fundamental figures against the official record, you verify core queries against expected results. Just as the basic scoreboard is checked before every broadcast because everything on air cites it, core queries deserve verification first because every later view builds on their logic. Just as a standings table that miscounts one match discredits the whole broadcast, one wrong aggregate undermines trust in the entire dashboard. This shows why a correct foundation precedes advanced analytics.

sql

-- analytics.sql : PHASE 1 core reporting queries.

-- 1) Team standings: matches, total runs, average runs per delivery.
SELECT t.team_name,
       COUNT(DISTINCT d.match_id)            AS matches,
       SUM(d.runs)                           AS total_runs,
       ROUND(AVG(d.runs), 2)                 AS avg_runs_per_ball
FROM teams t
JOIN players p   ON p.team_id = t.team_id
JOIN deliveries d ON d.batsman_id = p.player_id
GROUP BY t.team_name
ORDER BY total_runs DESC, t.team_name ASC;

-- 2) Player run leaderboard with a deterministic tie-breaker.
SELECT p.player_name, t.team_name,
       SUM(d.runs)            AS runs,
       COUNT(*)               AS balls_faced,
       ROUND(SUM(d.runs) * 100.0
             / NULLIF(COUNT(*), 0), 1) AS strike_rate
FROM players p
JOIN teams t      ON t.team_id = p.team_id
JOIN deliveries d ON d.batsman_id = p.player_id
GROUP BY p.player_name, t.team_name
ORDER BY runs DESC, p.player_name ASC;

-- 3) Per-venue scoring summary.
SELECT v.venue_name,
       COUNT(DISTINCT m.match_id) AS matches,
       SUM(d.runs)                AS total_runs,
       ROUND(AVG(d.runs), 2)      AS avg_runs_per_ball
FROM venues v
JOIN matches m    ON m.venue_id = v.venue_id
JOIN deliveries d ON d.match_id = m.match_id
GROUP BY v.venue_name
ORDER BY total_runs DESC, v.venue_name ASC;

Phase 2 — Core Features

In Phase 2 you add the advanced analytics that distinguish a real dashboard. Build a top scorer per team using a ROW_NUMBER window function filtered in a CTE, a running cumulative scoreline per match using SUM OVER ordered by ball, and a player form comparison using LAG to show each innings against the previous one. These apply Modules 4 and 6 directly.

Pay close attention to the window definitions: partition by the right entity, order deterministically with tie-breakers, and filter window results in an outer query. Use NULLIF and COALESCE to keep rates and deltas clean at the edges. Verify that the running totals climb correctly and that the per-team top scorer is unique, confirming your advanced logic is sound.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the team adding the graphics fans remember: the Worm showing cumulative runs, the per-team star performer, the form line tracking a player across matches. Just as these require more skill than a basic total yet make the broadcast compelling, window functions deliver the analytics that elevate your dashboard. Just as each graphic must be precisely defined to be accurate, each window needs the right partition, order, and frame. Just as the Worm and the form line are recomputed from the same ball-by-ball feed rather than kept as separate spreadsheets, the analytics views derive from base tables at query time and never store stale copies. Just as a mis-partitioned form line would blend two players' careers, a wrong PARTITION BY silently mixes entities, so each window's grouping deserves a test. This shows why advanced analytical features build directly on a correct foundation.

sql

-- analytics.sql : PHASE 2 advanced analytics.

-- 4) Top scorer per team (unique, even amid ties) via ROW_NUMBER + CTE.
WITH player_runs AS (
    SELECT p.team_id, p.player_id, p.player_name, SUM(d.runs) AS runs
    FROM players p JOIN deliveries d ON d.batsman_id = p.player_id
    GROUP BY p.team_id, p.player_id, p.player_name
),
ranked AS (
    SELECT team_id, player_name, runs,
           ROW_NUMBER() OVER (PARTITION BY team_id
                              ORDER BY runs DESC, player_id) AS rn
    FROM player_runs
)
SELECT t.team_name, r.player_name, r.runs
FROM ranked r JOIN teams t ON t.team_id = r.team_id
WHERE r.rn = 1
ORDER BY r.runs DESC;

-- 5) Running cumulative scoreline within each match (the "Worm").
SELECT match_id, over_no, ball_no, runs,
       SUM(runs) OVER (PARTITION BY match_id
                       ORDER BY over_no, ball_no) AS running_total
FROM deliveries
ORDER BY match_id, over_no, ball_no;

-- 6) Player form: each match's runs vs the player's previous match (LAG).
WITH per_match AS (
    SELECT batsman_id, match_id, SUM(runs) AS match_runs
    FROM deliveries GROUP BY batsman_id, match_id
)
SELECT pm.batsman_id, pm.match_id, pm.match_runs,
       LAG(pm.match_runs, 1, 0) OVER (PARTITION BY pm.batsman_id
                                      ORDER BY pm.match_id) AS prev_match,
       pm.match_runs - LAG(pm.match_runs, 1, 0)
            OVER (PARTITION BY pm.batsman_id ORDER BY pm.match_id) AS delta
FROM per_match pm
ORDER BY pm.batsman_id, pm.match_id;

Phase 3 — Polish & Packaging

In Phase 3 you package the analytics layer for reuse and review. Wrap your key reports as CREATE VIEW definitions so consumers query them with a simple SELECT, add comments documenting each view's purpose and columns, and confirm with EXPLAIN that a heavy query uses your indexes. Finally, write a short README explaining how to build and query the project end to end.

Analogy🏏Cricket

🏏 Think of it like cricket: Packaging your analytics as views is like turning a season's raw analysis into a polished match-programme anyone can pick up and read. Just as a programme presents ready-made tables so a fan need not recompute anything, a CREATE VIEW wraps a complex report so consumers query it with a simple SELECT. Just as each table carries a caption explaining what it shows, comments document each view's purpose and columns. Just as a producer double-checks the heavy graphics will render fast enough for live broadcast, you confirm with EXPLAIN that a costly query actually uses your indexes. Just as the programme opens with a note on how to read it, a short README explains how to build and query the project end to end. Just as a well-packaged programme lets newcomers use the work without the analyst present, packaged views make your analytics reusable and reviewable. This reveals why polishing and packaging is what makes analytical work last.

sql

-- analytics.sql : PHASE 3 views, documentation, verification.

-- Encapsulate the leaderboard as a reusable, documented view.
CREATE OR REPLACE VIEW player_leaderboard AS
-- Purpose: per-player runs, balls, and strike rate across the season.
-- Columns: player_name, team_name, runs, balls_faced, strike_rate.
SELECT p.player_name, t.team_name,
       SUM(d.runs)            AS runs,
       COUNT(*)               AS balls_faced,
       ROUND(SUM(d.runs) * 100.0 / NULLIF(COUNT(*), 0), 1) AS strike_rate
FROM players p
JOIN teams t      ON t.team_id = p.team_id
JOIN deliveries d ON d.batsman_id = p.player_id
GROUP BY p.player_name, t.team_name;

-- Encapsulate team standings as a view.
CREATE OR REPLACE VIEW team_standings AS
-- Purpose: per-team match count, total runs, and run rate.
SELECT t.team_name,
       COUNT(DISTINCT d.match_id) AS matches,
       SUM(d.runs)                AS total_runs,
       ROUND(AVG(d.runs), 2)      AS avg_runs_per_ball
FROM teams t
JOIN players p    ON p.team_id = t.team_id
JOIN deliveries d ON d.batsman_id = p.player_id
GROUP BY t.team_name;

-- Consumers now query simply:
SELECT * FROM player_leaderboard ORDER BY runs DESC LIMIT 10;
SELECT * FROM team_standings     ORDER BY total_runs DESC;

-- Verify a heavy query uses an index rather than scanning.
EXPLAIN SELECT SUM(runs) FROM deliveries WHERE batsman_id = 7;
-- Expect an Index Scan on idx_deliveries_bat.

-- README.md (sketch):
-- 1) psql proj -f schema.sql   2) psql proj -f seed.sql
-- 3) psql proj -f analytics.sql  4) query the views.

Extension Challenge: Extend the dashboard with a materialised view for the most expensive aggregation and refresh it after loading new data, comparing query speed against the plain view. Then add a recursive CTE that generates a gap-free date series and LEFT JOIN it to daily run totals so days with no matches still appear as zero, producing a complete time series for charting.

Evaluation Rubric

Schema design: at least five normalised tables with correct primary and foreign keys, precise data types, and meaningful CHECK constraints that reject invalid data.
Data integrity: seed data loads in dependency order without violations, and attempting an invalid insert is correctly refused by the constraints.
Core queries: standings, leaderboard, and venue summary return correct, verified results with deterministic ordering and proper tie-breakers.
Advanced analytics: window functions produce a unique top scorer per team, a correct running scoreline, and a valid form comparison using LAG.
Reusability and documentation: at least two views encapsulate reporting logic with clear comments, and a README explains how to build and query the project.
Performance: foreign keys and hot columns are indexed, and EXPLAIN confirms at least one heavy query uses an index rather than a full sequential scan.
Code quality: queries are aliased, readable, and use CTEs for staged logic, with NULLIF and COALESCE handling edge cases so no query crashes or returns blanks.