Practice — advanced analytics on IPL data — SQL & Relational Databases

What You'll Build

In this exercise you will act as a senior data analyst for the IPL Analytics Bureau — the fictional analytics division that produces official IPL tournament statistics. Using an IPL-themed dataset containing seasons, matches, teams, players, deliveries, and batting/bowling scorecards, you will build a comprehensive tournament analytics report using the full M2 advanced SQL toolkit: window functions (ROW_NUMBER, RANK, DENSE_RANK), offset functions (LAG, LEAD), date functions (DATE_TRUNC, period comparisons), string functions (CASE, COALESCE, SPLIT_PART), and conditional aggregation for pivoted reports. The final deliverable is a reusable analytics script that could run nightly in a production pipeline.

The exercise is structured to mirror real data engineering work: you will first build and validate the schema, then write incremental analytical queries that build on each other. Each step produces a named query result that feeds the next step — exactly how dbt models chain transformation steps. By the end, you will have a working 'IPL Season Report' query that a cricket analyst could use to produce the official end-of-season batting and bowling summary tables.

Analogy🏏Cricket

🏏 Think of it like cricket: This exercise runs like a proper tournament bureau's production week, and the step order is the point. Step 1 is the pitch inspection before play: you verify the ground truth — no orphaned scorecard lines, no impossible totals — because an analysis built on a corrupt book is a match played on a dangerous pitch: everything after it is invalidated. Step 2 is the specialist coaches' reports: batting summaries with rankings and form lines, each an independent, checkable piece of work using window functions over the validated data. Step 3 is the selectors' composite: batting and bowling folded into one all-rounder view via conditional aggregation — the wide wall chart built from the long book. Step 4 is the match referee's reconciliation: the chart's totals must re-add to the book's totals exactly, or something was dropped or double-counted on the way. Validate, analyse, combine, reconcile — every production pipeline plays in that order.

All data is cricket-themed and India-specific: IPL teams (CSK, MI, RCB, KKR, DC, SRH, PBKS, RR), real player names (MS Dhoni, Rohit Sharma, Virat Kohli, Jasprit Bumrah), and realistic match statistics. The queries you write will produce results consistent with real IPL tournament statistics, giving you both technical practice and domain familiarity with the kind of data that data engineers in the cricket analytics industry work with daily.

Prerequisites

Setup & Project Structure

Create the ipl_analytics database and run the setup script to create six tables and load IPL-themed sample data. The schema is deliberately denormalised to reflect a realistic source database structure where data engineers encounter redundancy and need to clean and normalise before analysis. Keep all your query solutions in a single file ipl_analytics.sql — this file is your portfolio deliverable for this module.

Analogy🏏Cricket

🏏 Think of it like cricket: this setup is like inheriting a franchise's raw scoring archive before an analytics project — six boxes of records where team names are spelled three ways and player details are copied redundantly onto every card. Just as real archives are messy because they were built for recording matches, not for analysis, this schema is deliberately denormalised to mirror the source databases data engineers actually meet, where cleaning and normalising come before any insight. Just as an analyst hired by an IPL franchise would keep every derivation in one working file — so the head coach can audit exactly how each conclusion was reached — you keep all solutions in a single ipl_analytics.sql, which doubles as your portfolio deliverable, the way a match-analysis dossier proves a cricket analyst's craft to the next employer. The payoff: practising on realistically imperfect data, with a reviewable artefact at the end, is what turns textbook SQL into the working habits of a professional data engineer.

Step 1 — Foundation: Data Quality Checks

Before writing analytical queries, run the validation queries below to confirm data integrity. In a production pipeline, these checks would run as assertions at the end of the ingestion step — any failure stops the pipeline and alerts the on-call engineer. Verify that all matches reference valid teams, all batting/bowling records reference valid players and matches, and no unexpected NULLs exist in required columns. Document any anomalies found (the dataset includes one intentional anomaly for you to find and explain).

Analogy🏏Cricket

🏏 Think of it like cricket: data quality checks are the match referee's pre-play inspection: before a ball is bowled, confirm both teams are registered, every named player exists on an official roster, and no scorecard has blanks where mandatory entries belong. Just as a match played with an unregistered player invalidates results no matter how well it was scored afterwards, analytics built on batting records referencing non-existent players or matches produces confidently wrong numbers. Just as the referee's inspection is a standing gate — play does not start until it passes — production pipelines run these validations as assertions at the end of ingestion, and any failure stops the pipeline and pages the on-call engineer rather than letting bad data flow downstream. And just as a sharp-eyed referee documents irregularities in the match report, you should document the one intentional anomaly hidden in this dataset when you find it. The payoff: the habit of proving inputs valid before analysing them — the discipline that separates trustworthy pipelines from lucky ones.

Step 2 — Core Logic: Season Aggregation with Window Functions

Write the four analytical queries below, each building on the dataset validated in Step 1. Query 2a produces the season batting summary (total runs, innings, average, strike rate) per player per season. Query 2b uses ROW_NUMBER to deduplicate any players who appear in both seasons with different team codes — keeping their most recent season record. Query 2c uses RANK to produce the IPL batting leaderboard for 2024. Query 2d uses LAG to compute each player's season-on-season runs change. Verify each result before proceeding.

Analogy🏏Cricket

🏏 Think of it like cricket: these four queries are a full analytics session on a season's records, each drill mapping to a real selector's task. Just as a season review starts by tallying every batter's runs, innings, average, and strike rate (query 2a), then resolves players who appear under two team codes after a mid-auction trade by keeping their most recent record — exactly the ROW_NUMBER dedup, sort by recency and keep row one (2b) — the pipeline mirrors how boards clean transfer-season data. Just as the official Orange Cap standings use competition ranking, where two batters tied on runs share a rank and the next place is skipped, RANK builds the 2024 leaderboard (2c). And just as a selector reads form by comparing each player's runs this season against last season, LAG pulls the previous season's total onto the current row for a season-on-season change (2d). Verifying each result before moving on is checking your tally against the published scorecard after each drill. The payoff: the four workhorse patterns of analytical SQL rehearsed on data shaped like the real thing.

Step 3 — Integration: Full Season Report with Pivot

Combine the Season 2024 batting and bowling statistics into a single All-Rounder Report using conditional aggregation. This pivot shows each player's batting runs, strike rate, bowling wickets, and economy rate in one row — the format used in the IPL's official player cards. Also produce the 'Top Performers by Phase' report using DATE_TRUNC to segment the tournament into phases: Early League (matches 1-10), Mid League (11-20), Late League (21+), and Playoffs. Include COALESCE and CASE for display-ready output with no NULLs in the final result.

Analogy🏏Cricket

🏏 Think of it like cricket: An all-rounder report is exactly why pivots exist. In the long-format tables, Ravindra Jadeja is scattered: his batting lines live in one set of rows, his bowling figures in another — flip pages forever to judge him as a package. The selectors want one line: Jadeja | runs | strike rate | wickets | economy, side by side. Conditional aggregation builds that line in a single pass — 'sum runs from his batting rows, count wickets from his bowling rows' — each column a differently-filtered aggregate over the same grouped scan. The Step 4 reconciliation is the scorer's iron law of chart-making: after rotating the book into the chart, total the runs column and check it equals the book's grand total. If the chart says less, a player fell through a join; if more, someone was double-counted by a fan-out. The pivot changes the shape of the data — it must never change its sum.

Step 4 — Testing & Verification

Run the reconciliation checks below to verify the Step 3 all-rounder report is internally consistent. Total batting runs from the all-rounder report must equal total batting runs from the Step 2a season_batting CTE. Total wickets from the all-rounder report must equal total wickets from a direct ipl_bowling aggregation. Any discrepancy indicates an incorrect JOIN or a missing COALESCE causing NULLs to propagate. Document the anomaly found in Step 1 and explain how the WHERE b.batting_pos IS NOT NULL clause handles it.

Analogy🏏Cricket

🏏 Think of it like cricket: final reconciliation is how a tournament's official statistician signs off the season book: the runs credited to all-rounders in the combined report must equal the runs in the batting ledger, and their wickets must equal the bowling ledger's total — three independently kept tallies that must agree before anything is published. Just as a mismatch tells the scorer that a card was double-counted or a player's bowling figures never got attached to their batting record, a discrepancy here indicates a JOIN that multiplied rows or a missing COALESCE letting NULLs eat values silently. Just as the statistician must also explain every irregularity noted during the season in the final report, you document the Step 1 anomaly and explain precisely how WHERE b.batting_pos IS NOT NULL handles it — an auditable justification, not a silent exclusion. The payoff: results cross-checked from independent directions and anomalies explained in writing — the standard that lets a data engineer promote numbers to production with confidence.