Exercise 2 — write the analytical queries — SQL & Relational Databases

What You'll Build

In this exercise you write the production analytical queries that power each of the six CricketVerse functional requirements, building on the schema from Exercise 1. Each query applies techniques from across the course: JOINs and aggregations (Module 1), window functions and conditional logic (Module 2), and CTEs for readable multi-step logic. Attempt each query yourself from the functional requirement before reading the reference solution. The goal is fluency in translating a business question into correct, efficient SQL.

The six queries progress in complexity: FR1 (career stats) is a straightforward aggregation; FR2 (head-to-head) is a filtered aggregation on the deliveries fact table; FR3 (venue analysis) combines aggregation with conditional logic; FR4 (leaderboard) is a ranked aggregation; FR5 (partnerships) requires self-referential logic on consecutive batters; and FR6 (form analysis) requires a window function for rolling averages. Together they exercise the full analytical SQL toolkit the course has taught.

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.

FR1 — Career Batting Statistics per Format

Write a query that produces each player's career batting statistics broken down by format (Test, ODI, T20): innings count, total runs, batting average (runs per dismissal), strike rate (runs per 100 balls), and centuries. This requires aggregating the deliveries fact table up to the player-format level, handling not-out innings correctly in the average calculation.

The FR1 query demonstrates the critical edge case in cricket statistics: batting average divides total runs by the number of dismissals, not the number of innings. A batter who scored 50 runs across 5 innings but was only out twice has an average of 25 (50/2), not 10 (50/5). The NULLIF(COUNT(*) FILTER (WHERE pi.was_dismissed), 0) protects against division by zero for a batter who was never dismissed. The FILTER clause (Module 2's conditional aggregation) cleanly separates the dismissal count from the innings count without subqueries.

Analogy🏏Cricket

🏏 Think of it like cricket: The batting-average edge case is cricket's own not-out rule expressed in SQL. A batting average is runs divided by dismissals, not by innings — a batter with 300 runs from five innings but only two dismissals averages 150, not 60, because the three not-outs never cost a wicket. Divide by COUNT(*) of innings and you have written a plausible-looking query that quietly slanders every finisher in the league. And the batter who has never been dismissed — 200 career runs, zero dismissals — is the division-by-zero trap in pads: their average is mathematically undefined (cricket lore says 'infinite'), and NULLIF(dismissals, 0) is the scorer's dash on the card: render the undefined honestly as NULL rather than crashing the report or inventing a zero. The lesson generalises: encode the domain's real counting rules, not the ones that make the SQL shortest.

FR2 — Head-to-Head: Batter vs Bowler

Write a query that, given a batter and a bowler, returns their head-to-head record: balls faced, runs scored, dismissals, average, and strike rate. This is the query that the composite index idx_deliveries_h2h was designed to support.

Analogy🏏Cricket

🏏 Think of it like cricket: the head-to-head query is the broadcaster's classic pre-delivery graphic — 'Kohli vs Anderson: 190 balls, 260 runs, dismissed 3 times, average 86.7, strike rate 136' — and writing it teaches you how that card is really made. Every number on it is an aggregate over the deliveries where this batter faced this bowler: balls faced is a COUNT, runs a SUM, dismissals a conditional count, average and strike rate derived ratios. The composite index idx_deliveries_h2h is the analyst's pre-sorted binder filed by (batter, bowler): just as a TV statistician can flip straight to the 'Kohli vs Anderson' tab instead of re-reading every scorebook, the database seeks directly to the matching index range instead of scanning millions of delivery rows — the query and the index were designed as a pair. Practising this query closes the loop begun in the schema exercise: you built the index for a promised access pattern, and now you prove the promise by writing the exact WHERE clause it serves. The payoff: you experience how a well-designed composite index turns a two-player filter over millions of balls into an instant broadcast-ready stat card.

FR3 — Venue Analysis

Write a query that analyses each venue: total matches hosted, average first-innings score, and the win rate for teams batting first versus batting second. This combines aggregation with conditional logic to compute the bat-first advantage at each ground.

Analogy🏏Cricket

🏏 Think of it like cricket: venue analysis is the question every captain asks at the toss: 'what does this ground do?' Eden Gardens averages 180 first innings with chasers winning 60%, while Chepauk's turning track rewards batting first — your query computes that dossier for every ground. Total matches hosted is a plain COUNT grouped by venue; average first-innings score aggregates only the first innings of each match; and the bat-first-vs-chase win rate is where conditional logic earns its keep — a CASE expression inside an aggregate counts bat-first and chase wins separately, like an analyst sorting one stack of results into two piles before computing each pile's percentage. Just as a coach never judges a ground on one match but on its full history, GROUP BY venue folds every fixture at each ground into one summary row. Practising this query teaches the workhorse pattern of analytical SQL: aggregation combined with conditional counting (SUM(CASE WHEN ...)), the same technique behind powerplay comparisons, home-advantage studies, and day-night splits. The payoff: you can turn raw results into the toss-decision dossier that actually changes how a captain plays.

FR4 — Real-Time Season Leaderboard

Write a query producing the season leaderboard: top run-scorers in a given season with their rank, runs, innings, and average. This is the query that will be materialised and cached in Redis for sub-50ms reads, per the non-functional requirement.

Analogy🏏Cricket

🏏 Think of it like cricket: the season leaderboard query is the Orange Cap standings — top run-scorers ranked with runs, innings, and average — and writing it teaches the full ranking pattern: aggregate each batter's season runs and innings, derive the average, then a window function assigns rank, just as the league table lists position. But the operational insight is in how it will be served. Millions check the standings obsessively, yet the numbers only change when an innings ends. Recomputing the full aggregation for every glance would be like the scorers re-adding the whole season's books for each spectator; instead, the plan is to materialise the result and cache it in Redis, like painting the standings on the stadium's big screen and repainting only when scores actually change — which is how a heavy analytical query meets a sub-50ms read requirement. Practising this query completes the pipeline you have been building all course: correct SQL first, then a serving strategy matched to read traffic. The payoff: one well-written query becomes both the truth (in PostgreSQL) and the instant answer (in Redis) for the season's most-watched statistic.

FR5 and FR6 — Partnerships and Form Analysis

FR5 (partnerships) analyses runs scored while two specific batters were at the crease together. FR6 (form analysis) uses a window function to compute a rolling average over each player's last N innings — directly applying the window function techniques from Module 2.

FR5 demonstrates the LEAST/GREATEST normalisation trick for symmetric relationships: a partnership between players A and B is the same regardless of which one is on strike, so LEAST(batter_id, non_striker_id) and GREATEST(batter_id, non_striker_id) produce a canonical pair ordering that groups (A,B) and (B,A) together. Without this normalisation, the same partnership would be counted as two separate pairs depending on who was facing each ball.

Analogy🏏Cricket

🏏 Think of it like cricket: The LEAST/GREATEST trick solves the partnership identity problem every scorer knows: the Kohli–Rahul stand is the same partnership whether Kohli or Rahul took strike first, yet naive storage records (Kohli, Rahul) and (Rahul, Kohli) as two different pairs, splitting one partnership's runs across two phantom rows. Normalising with LEAST and GREATEST — always file the pair under the lower player ID first — is the scorebook convention of always writing the partnership under a canonical ordering, so every lookup and aggregation converges on one row per real-world pair. FR6's rolling frame is the form guide's 'last five innings' column: ROWS BETWEEN 4 PRECEDING AND CURRENT ROW slides a five-innings window down each player's chronological record, recomputing the average at every step — a moving snapshot of current form, unlike the career average, which is the whole book compressed into one unmoving number.

FR6 is a direct application of Module 2's window functions. The ROWS BETWEEN 4 PRECEDING AND CURRENT ROW frame computes a rolling average over the current innings plus the four preceding innings — a 5-innings moving average that reveals form trends. The PARTITION BY isc.batter_id resets the window for each player, and ORDER BY match_date, match_id ensures the rolling window follows chronological order. The second window (ROWS UNBOUNDED PRECEDING) computes a running career total — both windows in a single pass over the data.