IPL Season Summary Dashboard

What You'll Build

In this exercise you will build the query layer behind an IPL season summary dashboard. Working from a richer deliveries table, you will write grouped, aggregated queries that produce team totals, player leaderboards, venue breakdowns, and monthly scoring trends, exactly the figures a real cricket dashboard displays. Every query applies the aggregation, grouping, filtering, and date skills from this module together.

By the end you will have a single SQL file that, run against the seeded data, outputs every summary panel a season dashboard needs. This mirrors how analysts actually work: the dashboard's charts are just visualisations of GROUP BY queries underneath, so writing those queries cleanly is the real skill that makes a reporting layer accurate, fast, and easy to maintain.

The emphasis is on producing correct, presentation-ready results. You will choose the right COUNT forms, guard against NULLs and empty sets, filter at the correct stage with WHERE versus HAVING, and order leaderboards deterministically. Predict each panel's output before running it, then verify, so you build genuine confidence that your numbers are right rather than merely plausible.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the analytics team assembling the broadcast's end-of-day statistics package. Just as they compile separate panels, top scorers, team totals, venue records, and trend graphs, from the same ball-by-ball data, you build separate grouped queries from one deliveries table. Just as each panel must be accurate before it airs to millions, each query must be verified before it feeds the dashboard. Just as the package tells the day's story through numbers, your query layer turns raw deliveries into the season's narrative. Just as a wrong figure on air is caught by cross-checking panels against each other, team totals summing to the tournament total, your queries can be validated against one another for consistency. Just as the package is rebuilt fresh from the feed each day, every dashboard number is derived from raw rows at query time, never hand-edited. This reveals why disciplined aggregation is the backbone of every stats broadcast.

Prerequisites

A working PostgreSQL setup from the Module 1 exercise, with the psql client available to run SQL files against a local database.
Completion of this module's lessons on aggregate functions, GROUP BY, HAVING, and string and date functions, all applied directly below.
Comfort writing SELECT queries with WHERE, ORDER BY, and LIMIT, since every dashboard panel layers grouping on top of those basics.
A text editor to maintain a single dashboard.sql file, keeping the whole reporting layer reproducible and easy to rerun from scratch.

Setup & Project Structure

You will create a fresh database and a single deliveries table enriched with season, match_date, and dismissal columns so every aggregation and date query has realistic data to work on. As before, the schema lives in a script for reproducibility, and all dashboard panels live in one queries file you can run end to end to regenerate every figure at once.

Analogy🏏Cricket

🏏 Think of it like cricket: Building the deliveries table with season, match_date, and dismissal columns is like laying out a season-long scorebook designed for every question you will ask. Just as a good scorebook records not only runs but the date and manner of each dismissal so any summary can be drawn later, your enriched table carries the extra columns every aggregation and date query will need. Just as a scorer keeps the blank scorebook template so next season starts identically, your schema lives in a rerunnable script. Just as all the tournament's summary graphics are produced from that one book, every dashboard panel lives in one queries file you can run end to end. Just as reopening the book regenerates any figure on demand, rerunning the file regenerates every number at once. This reveals why a well-designed table and scripted panels make a dashboard reproducible.

Keeping the schema and the dashboard queries in separate, rerunnable files reflects real reporting practice, where the data layer and the report definitions are version-controlled artifacts. Anyone can rebuild the exact dashboard inputs from the schema file and reproduce every panel from the queries file, making the whole summary auditable rather than a pile of one-off statements typed into a shell.

bash

# Create a fresh database for the dashboard exercise.
dropdb ipl_dashboard 2>/dev/null; createdb ipl_dashboard

# Project files.
mkdir -p ipl_dashboard_lab && cd ipl_dashboard_lab
touch schema.sql        # creates and seeds the deliveries table
touch dashboard.sql     # all dashboard panel queries

# Load and run.
psql ipl_dashboard -f schema.sql
psql ipl_dashboard -f dashboard.sql

# Verify a single figure quickly:
psql ipl_dashboard -c "SELECT COUNT(*) FROM deliveries;"   # expect 12

Step 1 — Foundation

Step one defines and seeds the deliveries table. It carries the columns every dashboard panel needs: team and player names, venue, runs, strike rate, season, match date, and a nullable dismissal. Twelve rows across two seasons and several venues give the grouped queries enough variety to produce meaningful, verifiable summaries rather than trivial single-group results.

Defining clear types and a CHECK constraint on runs ensures the data is valid before any aggregate runs, so your totals and averages can be trusted. Running this schema file once builds a clean, reproducible starting point, the same disciplined foundation you established in Module 1, now extended with the date and season dimensions this module's queries exercise.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the groundstaff and scorers preparing before the season opener. Just as they set up the pitch, boundary, and a fresh scorebook with columns for every stat to be recorded, the schema lays out typed columns for runs, dates, and dismissals. Just as a complete scorebook lets every later statistic be computed accurately, a complete table lets every dashboard panel aggregate correctly. Just as the same setup repeats reliably each match, rerunning the schema rebuilds identical data. Just as the scorers agree before the first ball how a wide or a no-ball will be recorded, the schema fixes types and NULL conventions up front so every later aggregate interprets the data identically. Just as an incomplete scorebook column ruins every statistic that depends on it, a mistyped or missing column undermines every panel built on the table. This shows why a thorough foundation makes every later summary dependable.

sql

-- schema.sql : run with  psql ipl_dashboard -f schema.sql
DROP TABLE IF EXISTS deliveries;

CREATE TABLE deliveries (
    delivery_id  INTEGER PRIMARY KEY,
    batsman      TEXT    NOT NULL,
    team_name    TEXT    NOT NULL,
    venue        TEXT    NOT NULL,
    runs_scored  INTEGER NOT NULL CHECK (runs_scored >= 0),
    strike_rate  NUMERIC,
    season       INTEGER NOT NULL,
    match_date   DATE    NOT NULL,
    dismissal    TEXT          -- NULL means not out
);

INSERT INTO deliveries
 (delivery_id,batsman,team_name,venue,runs_scored,strike_rate,season,match_date,dismissal) VALUES
 (1, 'Rohit Sharma',     'Mumbai Indians',      'Wankhede',    72, 142.0, 2024, DATE '2024-03-05', NULL),
 (2, 'Suryakumar Yadav', 'Mumbai Indians',      'Wankhede',    61, 168.5, 2024, DATE '2024-03-05', NULL),
 (3, 'Hardik Pandya',    'Mumbai Indians',      'Chinnaswamy', 46, 164.2, 2024, DATE '2024-03-22', 'caught'),
 (4, 'Virat Kohli',      'Royal Challengers',   'Chinnaswamy', 45, 128.5, 2024, DATE '2024-03-22', 'caught'),
 (5, 'Faf du Plessis',   'Royal Challengers',   'Chinnaswamy', 33, 121.0, 2024, DATE '2024-03-22', 'bowled'),
 (6, 'MS Dhoni',         'Chennai Super Kings', 'Chepauk',     18, 150.0, 2024, DATE '2024-04-10', 'bowled'),
 (7, 'Ruturaj Gaikwad',  'Chennai Super Kings', 'Chepauk',     58, 139.0, 2024, DATE '2024-04-10', NULL),
 (8, 'Rishabh Pant',     'Delhi Capitals',      'Kotla',       89, 151.0, 2024, DATE '2024-04-18', 'run out'),
 (9, 'KL Rahul',         'Lucknow Super Giants','Lucknow',     74, 133.9, 2024, DATE '2024-05-02', NULL),
 (10,'Rohit Sharma',     'Mumbai Indians',      'Wankhede',    40, 118.0, 2023, DATE '2023-04-11', 'bowled'),
 (11,'Virat Kohli',      'Royal Challengers',   'Chinnaswamy', 67, 145.0, 2023, DATE '2023-04-25', NULL),
 (12,'Shubman Gill',     'Gujarat Titans',      'Motera',      57, 129.4, 2023, DATE '2023-05-09', 'lbw');

SELECT COUNT(*) AS rows_loaded FROM deliveries;   -- expect 12

Step 2 — Core Logic

Now you build the core dashboard panels with grouped aggregates. Each query answers one panel's question: team totals, a player leaderboard, and a venue breakdown. You will pick COUNT forms deliberately, round averages for display, and order results so leaderboards rank correctly. Save these in dashboard.sql and predict each panel's shape before running to confirm your understanding.

These panels are pure GROUP BY in action, each partitioning the same deliveries data along a different dimension. Filtering to the 2024 season in WHERE keeps the panels focused, and ordering by an aggregate turns raw breakdowns into ranked tables. Together they demonstrate how a handful of grouped queries produce the bulk of what a season dashboard actually displays.

Analogy🏏Cricket

🏏 Think of it like cricket: Think of the broadcast analyst slicing the same day's play three ways for three on-screen panels. Just as they break the data down by team for one graphic, by player for another, and by venue for a third, each query groups the same table on a different column. Just as every panel must rank its entries clearly, you order each by its key aggregate. Just as the panels share one underlying scorecard, your queries share one deliveries table. Just as a panel showing only three teams instantly warns the analyst the feed is incomplete, eyeballing each query's row counts against known facts catches bugs early. Just as the director insists each graphic answers exactly one question cleanly, each query should group on exactly the dimension its panel needs. This reveals why a single clean dataset can power many distinct summary views.

sql

-- dashboard.sql : run with  psql ipl_dashboard -f dashboard.sql
-- Predict each panel's output before running, then verify.

-- PANEL 1: Team totals for the 2024 season.
SELECT team_name,
       COUNT(*)                  AS innings,
       SUM(runs_scored)          AS total_runs,
       ROUND(AVG(runs_scored),1) AS avg_runs,
       MAX(runs_scored)          AS best_score
FROM deliveries
WHERE season = 2024
GROUP BY team_name
ORDER BY total_runs DESC, team_name ASC;

-- PANEL 2: Player leaderboard for 2024 (runs across all innings).
SELECT batsman,
       COUNT(*)         AS innings,
       SUM(runs_scored) AS runs,
       COUNT(dismissal) AS times_out      -- NULLs (not out) skipped
FROM deliveries
WHERE season = 2024
GROUP BY batsman
ORDER BY runs DESC, batsman ASC;

-- PANEL 3: Venue breakdown across all seasons.
SELECT venue,
       COUNT(*)                  AS innings_played,
       ROUND(AVG(runs_scored),1) AS avg_runs,
       COUNT(DISTINCT team_name) AS teams_seen
FROM deliveries
GROUP BY venue
ORDER BY innings_played DESC, venue ASC;

Step 3 — Integration & Enhancement

Step three layers in HAVING, date functions, and tighter presentation to complete the dashboard. You will shortlist only qualified groups with HAVING, chart a monthly scoring trend with DATE_TRUNC, and build a clean top-scorer panel with a deterministic tie-breaker. These queries integrate every concept in the module, mirroring the more sophisticated panels a real dashboard includes beyond simple totals.

Pay attention to filter placement and NULL safety here. The season filter stays in WHERE while the qualification threshold goes in HAVING, the monthly trend groups on a truncated date, and COALESCE guards any empty-set total. Saving these enhanced panels alongside the core ones gives you a complete, reproducible dashboard query layer that demonstrates real command of aggregation and dates.

Analogy🏏Cricket

🏏 Think of it like cricket: Picture the analyst adding the advanced graphics that follow the basic scoreboard. Just as they show only players who met a minimum-innings cut for an awards panel and chart scoring momentum month by month, you apply HAVING thresholds and DATE_TRUNC trends. Just as these refined panels demand more care than a raw total, they require correct filter placement and NULL handling. Just as the full package tells a richer story, your integrated queries deliver a complete dashboard. Just as the momentum chart would mislead viewers if rain-abandoned fixtures were charted as scoring dips, your trend query must decide explicitly how missing and NULL data appear. Just as the awards cut-off is printed on the graphic so viewers trust the ranking, the HAVING threshold belongs visibly in the query, not buried in app code. This shows why combining the module's tools yields professional-grade reporting.

sql

-- dashboard.sql (continued)

-- PANEL 4: Qualified teams only, 2024, total above 100 runs.
SELECT team_name,
       SUM(runs_scored)          AS total_runs,
       ROUND(AVG(runs_scored),1) AS avg_runs
FROM deliveries
WHERE season = 2024                       -- row filter first
GROUP BY team_name
HAVING SUM(runs_scored) > 100             -- group filter after aggregation
ORDER BY total_runs DESC, team_name ASC;

-- PANEL 5: Monthly scoring trend across all seasons.
SELECT DATE_TRUNC('month', match_date)::date AS month_start,
       COUNT(*)                              AS innings,
       SUM(runs_scored)                      AS total_runs,
       ROUND(AVG(runs_scored),1)             AS avg_runs
FROM deliveries
GROUP BY DATE_TRUNC('month', match_date)
ORDER BY month_start;

-- PANEL 6: Top 3 scorers overall, deterministic tie-break, clean labels.
SELECT INITCAP(TRIM(batsman))      AS player,
       SUM(runs_scored)            AS total_runs
FROM deliveries
GROUP BY INITCAP(TRIM(batsman))
ORDER BY total_runs DESC, player ASC
LIMIT 3;

-- PANEL 7: Empty-set guard, a team that did not play in 2023.
SELECT COALESCE(SUM(runs_scored), 0) AS pbks_2023_runs
FROM deliveries
WHERE season = 2023 AND team_name = 'Punjab Kings';   -- returns 0, not NULL

Step 4 — Testing & Verification

Finally, rebuild and run everything from scratch, then check the panels against the expected results below. Because all logic lives in scripts, you can regenerate the entire dashboard data layer in seconds and confirm your figures are reproducible and correct rather than dependent on the order you happened to run statements in.

Analogy🏏Cricket

🏏 Think of it like cricket: Rebuilding the whole dashboard and checking each panel is like reprinting the entire season summary from the scorebook and confirming every graphic matches the record. Just as a broadcaster would not trust a figure that only appears if statements are run in a lucky order, you rebuild from scratch so no result depends on the sequence you happened to type. Just as the scorer reruns the totals and cross-checks the Orange Cap, run rates, and dismissal counts against the book, you check each panel against the expected results below. Just as the season book lets them regenerate every graphic in minutes, your scripted logic regenerates the entire dashboard data layer in seconds. Just as matching every panel to the record proves the summary is trustworthy, matching outputs confirms your figures are reproducible and correct. This reveals why a clean full rebuild is the real proof of a dashboard's integrity.

bash

# Rebuild and run end to end.
dropdb ipl_dashboard && createdb ipl_dashboard
psql ipl_dashboard -f schema.sql
psql ipl_dashboard -f dashboard.sql

# EXPECTED KEY RESULTS (verify a few panels):
# schema -> rows_loaded = 12
# PANEL 1 (2024 team totals), ordered by total_runs DESC:
#   Mumbai Indians      innings=3 total=179 avg=59.7 best=72
#   Royal Challengers   innings=2 total=78  avg=39.0 best=45
#   Chennai Super Kings innings=2 total=76  avg=38.0 best=58
#   Lucknow Super Giants innings=1 total=74 avg=74.0 best=74
#   Delhi Capitals      innings=1 total=89  avg=89.0 best=89
# PANEL 4 (HAVING > 100, 2024): only Mumbai Indians (179)
# PANEL 5 (monthly): 2023-04, 2023-05, 2024-03, 2024-04, 2024-05 buckets
# PANEL 6 (top 3 overall): Rohit Sharma 112, Virat Kohli 112, Rishabh Pant 89
#   (Rohit and Virat tie at 112; alphabetical tie-break puts Rohit... 
#    actually 'Rohit' > 'Rishabh'? verify ordering by running it)
# PANEL 7 (empty-set guard): pbks_2023_runs = 0

# Spot-check Mumbai's 2024 total directly:
psql ipl_dashboard -c \
"SELECT SUM(runs_scored) FROM deliveries WHERE season=2024 AND team_name='Mumbai Indians';"
# Expected: 179

Warning: A common error here is putting season = 2024 in HAVING instead of WHERE in Panel 4. It may return the right teams on this tiny dataset but forces the engine to group every season's rows before discarding them, and on real data it both slows the query and risks mixing seasons into the totals. Keep the season filter in WHERE.

Extension Challenge: Add a Panel 8 that computes each team's not-out rate, the share of innings where dismissal IS NULL, using COUNT and a NULL-aware ratio. Force decimal division and round to one decimal place, then order teams from highest not-out rate to lowest. This combines COUNT variations, NULL handling, and safe division into one analytical query.

A season dashboard's panels are GROUP BY queries; grouping the same table on team, player, venue, or month produces each distinct summary view.
Choosing COUNT forms deliberately, COUNT(*), COUNT(dismissal), COUNT(DISTINCT team_name), gives innings, dismissals, and category counts correctly.
Keep row-level filters like season in WHERE and group-level thresholds in HAVING, both for correctness and for performance on real data.
DATE_TRUNC buckets match dates into months to chart scoring trends, turning a raw date column into a clean time dimension for reporting.
Order leaderboards by an aggregate with a unique tie-breaker so rankings are deterministic and reproducible across every dashboard refresh.
Guard empty-set totals with COALESCE so panels show a clean zero instead of NULL when a filter happens to exclude every matching row.