Top Performers Ranking Query

What You'll Build

In this exercise you will build an analytical query suite that ranks IPL performers using subqueries and CTEs together. Working from a deliveries table, you will find batsmen above benchmarks with scalar and correlated subqueries, identify players with and without certain achievements using EXISTS and NOT EXISTS, and assemble a clean ranked leaderboard through a chained CTE pipeline that reads as a logical sequence.

By the end you will have a single SQL file that answers genuinely sophisticated questions: who outscored their own team's average, which players have never been dismissed, and the staged head-to-head and ranking computations that a real cricket analyst produces. This mirrors how subqueries and CTEs combine in practice to break multi-step analysis into readable, verifiable pieces.

The emphasis is on choosing the right construct for each question and composing them cleanly. You will use a scalar subquery for a global benchmark, a correlated subquery for per-team comparison, NOT EXISTS to dodge the NULL trap, and CTEs to stage the leaderboard. Predict each query's output before running, then verify, so the layered logic is something you trust rather than hope works.

Prerequisites

• A working PostgreSQL setup with the psql client, as built in the Module 1 exercise, ready to run SQL files against a local database.
• Completion of this module's lessons on scalar and inline subqueries, correlated subqueries with EXISTS, CTEs, and recursive CTEs, all applied below.
• Comfort with aggregates, GROUP BY, and joins from Modules 2 and 3, since the ranking queries combine grouping and subqueries together.
• A text editor to maintain a single ranking_lab.sql file so the whole schema and query suite stays reproducible and easy to rerun.

Setup & Project Structure

You will create a fresh database and a deliveries table carrying batsman, team, runs, and a nullable dismissal so EXISTS and NOT EXISTS have meaningful data. The schema lives in a rerunnable script and all analytical queries live in one file, keeping the whole lab reproducible. A few not-out players and a clear spread of scores give the benchmark and ranking queries real variety to work with.

This single-table design is deliberate: subqueries and CTEs shine at multi-stage analysis over a body of facts, so one rich table is enough to practise scalar benchmarks, per-group correlation, and staged ranking. Keeping schema and queries as version-controlled scripts reflects real reporting practice, where the analysis is reproducible and auditable rather than typed ad hoc into a shell.

# Fresh database for the ranking lab.
dropdb ipl_ranking 2>/dev/null; createdb ipl_ranking
mkdir -p ipl_ranking_lab && cd ipl_ranking_lab
touch schema.sql       # deliveries table, seeded
touch ranking_lab.sql  # subquery + CTE analytical queries

psql ipl_ranking -f schema.sql
psql ipl_ranking -f ranking_lab.sql

# Quick check:
psql ipl_ranking -c "SELECT COUNT(*) FROM deliveries;"   # expect 10

Step 1 — Foundation

Step one defines and seeds the deliveries table. It carries batsman, team_name, runs_scored, and a nullable dismissal, plus enough rows across three teams to make averages and rankings meaningful. Deliberate not-out rows, where dismissal is NULL, give the EXISTS and NOT EXISTS queries something real to detect, and a CHECK constraint keeps runs valid so your aggregates are trustworthy.

Running this schema once establishes the reproducible foundation for every analytical query that follows. As in earlier modules, defining clear types and constraints up front means the subqueries and CTEs you layer on top operate over clean, validated data, so any surprising result points to your query logic rather than to malformed input.

-- schema.sql : run with  psql ipl_ranking -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,
    runs_scored INTEGER NOT NULL CHECK (runs_scored >= 0),
    dismissal   TEXT          -- NULL means not out
);

INSERT INTO deliveries VALUES
    (1, 'Rohit Sharma',     'Mumbai Indians',      72, NULL),
    (2, 'Suryakumar Yadav', 'Mumbai Indians',      61, 'caught'),
    (3, 'Tilak Varma',      'Mumbai Indians',      28, 'bowled'),
    (4, 'Virat Kohli',      'Royal Challengers',   45, 'caught'),
    (5, 'Faf du Plessis',   'Royal Challengers',   33, NULL),
    (6, 'Glenn Maxwell',    'Royal Challengers',   12, 'run out'),
    (7, 'MS Dhoni',         'Chennai Super Kings', 18, NULL),
    (8, 'Ruturaj Gaikwad',  'Chennai Super Kings', 58, 'caught'),
    (9, 'Shivam Dube',      'Chennai Super Kings', 40, 'bowled'),
    (10,'Rohit Sharma',     'Mumbai Indians',      40, 'caught');

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

Step 2 — Core Logic

Now you write the core analytical queries using subqueries. A scalar subquery finds batsmen above the overall average, a correlated subquery finds those above their own team's average, and NOT EXISTS lists players who were never dismissed. Each targets a specific construct from the module, and you should predict the exact rows before running to confirm your understanding of how each behaves.

These queries show subqueries answering benchmark and presence questions directly. The scalar version computes one global figure, the correlated version recomputes per team, and the NOT EXISTS version safely handles the NULL dismissals where NOT IN could fail. Save them in ranking_lab.sql and verify that the per-team comparison yields different results than the global one.

-- ranking_lab.sql : run with  psql ipl_ranking -f ranking_lab.sql
-- Predict each result before running, then verify.

-- Q1. SCALAR subquery: batsmen above the overall average score.
SELECT batsman, team_name, runs_scored
FROM deliveries
WHERE runs_scored > (SELECT AVG(runs_scored) FROM deliveries)
ORDER BY runs_scored DESC, batsman ASC;

-- Q2. CORRELATED subquery: batsmen above THEIR OWN team's average.
SELECT d.batsman, d.team_name, d.runs_scored
FROM deliveries d
WHERE d.runs_scored > (
    SELECT AVG(d2.runs_scored)
    FROM deliveries d2
    WHERE d2.team_name = d.team_name        -- references outer row
)
ORDER BY d.team_name, d.runs_scored DESC;

-- Q3. NOT EXISTS: players who were NEVER dismissed (NULL-safe).
SELECT DISTINCT d.batsman
FROM deliveries d
WHERE NOT EXISTS (
    SELECT 1 FROM deliveries d2
    WHERE d2.batsman = d.batsman AND d2.dismissal IS NOT NULL
)
ORDER BY d.batsman;
-- A batsman dismissed in ANY row is excluded.

Step 3 — Integration & Enhancement

Step three assembles a ranked leaderboard with a chained CTE pipeline and adds head-to-head style comparison. You will build player_totals, then team_benchmarks, then a final ranked output that flags whether each player beat their team benchmark. This integrates aggregation, correlation logic, and CTEs into the kind of staged analysis a real leaderboard requires.

Watch the staging: each CTE has one clear responsibility and feeds the next, and the final query orders deterministically with a tie-breaker. Saving this alongside the core queries gives you a complete, reproducible ranking lab that demonstrates real command of composing subqueries and CTEs into readable multi-step analytics rather than an impenetrable nest.

-- ranking_lab.sql (continued)

-- Q4. Chained CTE leaderboard: totals -> benchmarks -> ranked flags.
WITH player_totals AS (
    SELECT batsman, team_name,
           SUM(runs_scored) AS total_runs,
           COUNT(*)         AS innings
    FROM deliveries
    GROUP BY batsman, team_name
),
team_benchmarks AS (
    SELECT team_name,
           ROUND(AVG(total_runs),1) AS team_avg_total
    FROM player_totals
    GROUP BY team_name
)
SELECT pt.batsman,
       pt.team_name,
       pt.total_runs,
       tb.team_avg_total,
       CASE WHEN pt.total_runs > tb.team_avg_total
            THEN 'above' ELSE 'at/below' END AS vs_team
FROM player_totals AS pt
JOIN team_benchmarks AS tb ON pt.team_name = tb.team_name
ORDER BY pt.total_runs DESC, pt.batsman ASC;

-- Q5. Top scorer per team using a correlated subquery in WHERE.
SELECT d.batsman, d.team_name, d.runs_scored
FROM deliveries d
WHERE d.runs_scored = (
    SELECT MAX(d2.runs_scored)
    FROM deliveries d2
    WHERE d2.team_name = d.team_name
)
ORDER BY d.team_name;

-- Q6. Players whose single best innings beat the overall average,
--     expressed cleanly with a CTE instead of a nested subquery.
WITH overall AS (
    SELECT AVG(runs_scored) AS avg_runs FROM deliveries
)
SELECT d.batsman, MAX(d.runs_scored) AS best
FROM deliveries d, overall o
GROUP BY d.batsman, o.avg_runs
HAVING MAX(d.runs_scored) > o.avg_runs
ORDER BY best DESC, d.batsman ASC;

Step 4 — Testing & Verification

Finally, rebuild and run everything, then check the queries against the expected results below. Because all logic lives in scripts, you can regenerate the whole lab in seconds and confirm the benchmark, presence, and ranking queries return exactly the right rows, with the correlated and scalar versions correctly differing where the data makes them differ.

# Rebuild and run end to end.
dropdb ipl_ranking && createdb ipl_ranking
psql ipl_ranking -f schema.sql
psql ipl_ranking -f ranking_lab.sql

# EXPECTED KEY RESULTS (verify a few):
# schema -> rows_loaded = 10
# Overall AVG runs = (72+61+28+45+33+12+18+58+40+40)/10 = 40.7
# Q1 (above overall avg 40.7): Rohit 72, Suryakumar 61, Ruturaj 58, Virat 45
#    (Faf 33, Shivam 40 are below/at; verify exact set)
# Q2 (above own team avg): differs per team; e.g. MI avg per innings
#    = (72+61+28+40)/4 = 50.25, so only Rohit 72 and Surya 61 qualify for MI.
# Q3 (never dismissed): players with NO dismissed row at all.
#    Rohit has a 'caught' row (id 10), so he is EXCLUDED. Faf (NULL) and
#    Dhoni (NULL) have no dismissed row -> included. Verify by running.
# Q5 (top per team): Rohit 72 (MI), Virat 45 (RCB), Ruturaj 58 (CSK).

# Spot-check overall average:
psql ipl_ranking -c "SELECT ROUND(AVG(runs_scored),1) FROM deliveries;"  # 40.7

• Scalar subqueries compare every row to one global benchmark, while correlated subqueries recompute per group, giving different, context-aware shortlists.
• NOT EXISTS expresses absence safely, excluding any player dismissed in even one innings, where a NOT IN against a nullable set could silently return nothing.
• Chained CTEs stage a leaderboard as named steps, totals then benchmarks then ranked flags, turning multi-step analysis into a readable pipeline.
• A correlated subquery in WHERE finds the top scorer per team by comparing each row to its own team's maximum, a clean per-group extreme pattern.
• Composing aggregation, correlation, and CTEs answers sophisticated questions while keeping each stage independently testable and verifiable.
• Predicting each query's rows before running, then verifying against expected results, builds trust in layered subquery and CTE logic.