SQL Analysis — IPL Sports Analytics¶

Setup

Load matches_clean.csv into table matches and deliveries_clean.csv into table deliveries. Join key: matches.id = deliveries.match_id.

Q1 — Top 10 Run Scorers of All Time¶

SELECT
    batter,
    SUM(batsman_runs)                       AS total_runs,
    COUNT(*) FILTER (WHERE is_ball_faced = 1) AS balls_faced,
    ROUND(SUM(batsman_runs) * 100.0 /
          NULLIF(COUNT(*) FILTER (WHERE is_ball_faced = 1), 0), 1) AS strike_rate,
    SUM(is_four)                            AS fours,
    SUM(is_six)                             AS sixes
FROM deliveries
GROUP BY batter
ORDER BY total_runs DESC
LIMIT 10;

MySQL note

MySQL doesn't support FILTER. Use SUM(CASE WHEN is_ball_faced = 1 THEN 1 ELSE 0 END) instead.

Sample output:

batter	total_runs	balls_faced	strike_rate	fours	sixes
V Kohli	6283	4879	128.8	553	218
S Dhawan	6244	4959	125.9	707	137
RG Sharma	5879	4576	128.5	526	240

Q2 — Best Strike Rate (min 500 balls)¶

SELECT
    batter,
    SUM(batsman_runs)                       AS runs,
    SUM(is_ball_faced)                      AS balls_faced,
    ROUND(SUM(batsman_runs) * 100.0 / SUM(is_ball_faced), 1) AS strike_rate
FROM deliveries
GROUP BY batter
HAVING SUM(is_ball_faced) >= 500
ORDER BY strike_rate DESC
LIMIT 10;

Q3 — Best Death-Over Batsmen (overs 16-20)¶

SELECT
    batter,
    SUM(batsman_runs)                       AS death_runs,
    SUM(is_ball_faced)                      AS death_balls,
    ROUND(SUM(batsman_runs) * 100.0 / SUM(is_ball_faced), 1) AS death_strike_rate
FROM deliveries
WHERE over >= 15        -- overs 16-20 (0-indexed: 15-19)
GROUP BY batter
HAVING SUM(is_ball_faced) >= 150
ORDER BY death_strike_rate DESC
LIMIT 10;

Q4 — Best Bowling Economy (min 1000 balls)¶

SELECT
    bowler,
    SUM(bowler_runs)                        AS runs_conceded,
    SUM(is_legal_ball)                      AS balls_bowled,
    ROUND(SUM(is_legal_ball) / 6.0, 1)      AS overs,
    SUM(is_bowler_wicket)                   AS wickets,
    ROUND(SUM(bowler_runs) / (SUM(is_legal_ball) / 6.0), 2) AS economy
FROM deliveries
GROUP BY bowler
HAVING SUM(is_legal_ball) >= 1000
ORDER BY economy ASC
LIMIT 10;

Q5 — Best Powerplay Bowlers (overs 1-6)¶

SELECT
    bowler,
    SUM(is_bowler_wicket)                   AS pp_wickets,
    ROUND(SUM(bowler_runs) / (SUM(is_legal_ball) / 6.0), 2) AS pp_economy
FROM deliveries
WHERE over < 6
GROUP BY bowler
HAVING SUM(is_legal_ball) >= 300
ORDER BY pp_wickets DESC, pp_economy ASC
LIMIT 10;

Q6 — Does Winning the Toss Help?¶

SELECT
    COUNT(*)                                AS total_matches,
    SUM(CASE WHEN toss_winner = winner THEN 1 ELSE 0 END)  AS toss_winner_won,
    ROUND(SUM(CASE WHEN toss_winner = winner THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS toss_win_pct
FROM matches
WHERE winner IS NOT NULL;

Sample output:

total_matches	toss_winner_won	toss_win_pct
950	489	51.5

Marginal toss advantage

Winning the toss gives only a ~1.5 percentage point edge (51.5% vs 50%). The toss matters far less than fans assume — useful myth-busting content for the platform.

Q7 — Bat or Field First After Winning the Toss?¶

SELECT
    toss_decision,
    COUNT(*)                                AS matches,
    SUM(CASE WHEN toss_winner = winner THEN 1 ELSE 0 END)  AS won,
    ROUND(SUM(CASE WHEN toss_winner = winner THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS win_pct
FROM matches
WHERE winner IS NOT NULL
GROUP BY toss_decision;

Sample output:

toss_decision	matches	won	win_pct
field	583	312	53.5
bat	367	177	48.2

Chasing wins

Teams that field first (chase) after winning the toss win 53.5% of the time vs 48.2% for batting first. In T20 cricket, chasing a known target is an advantage. This validates the common modern strategy of bowling first.

Q8 — Team Win Rates¶

WITH team_matches AS (
    SELECT team1 AS team FROM matches WHERE winner IS NOT NULL
    UNION ALL
    SELECT team2 AS team FROM matches WHERE winner IS NOT NULL
),
team_wins AS (
    SELECT winner AS team, COUNT(*) AS wins
    FROM matches WHERE winner IS NOT NULL
    GROUP BY winner
)
SELECT
    tm.team,
    COUNT(*)                                AS played,
    COALESCE(tw.wins, 0)                    AS wins,
    ROUND(COALESCE(tw.wins, 0) * 100.0 / COUNT(*), 1) AS win_pct
FROM team_matches tm
LEFT JOIN team_wins tw ON tm.team = tw.team
GROUP BY tm.team, tw.wins
ORDER BY win_pct DESC;

Q9 — Highest Scoring Venues (Par Score)¶

WITH innings_totals AS (
    SELECT
        m.venue,
        d.match_id,
        d.inning,
        SUM(d.total_runs)                   AS innings_score
    FROM deliveries d
    JOIN matches m ON d.match_id = m.id
    WHERE d.inning = 1     -- first innings = "par score"
    GROUP BY m.venue, d.match_id, d.inning
)
SELECT
    venue,
    COUNT(*)                                AS matches,
    ROUND(AVG(innings_score), 0)            AS avg_first_innings_score
FROM innings_totals
GROUP BY venue
HAVING COUNT(*) >= 20
ORDER BY avg_first_innings_score DESC
LIMIT 10;

Q10 — Best Batsman-vs-Bowler Matchups¶

-- Which batsmen dominate specific bowlers? (min 30 balls faced)
SELECT
    batter,
    bowler,
    SUM(batsman_runs)                       AS runs,
    SUM(is_ball_faced)                      AS balls,
    SUM(is_bowler_wicket)                   AS dismissals,
    ROUND(SUM(batsman_runs) * 100.0 / SUM(is_ball_faced), 1) AS strike_rate
FROM deliveries
GROUP BY batter, bowler
HAVING SUM(is_ball_faced) >= 30
ORDER BY strike_rate DESC
LIMIT 15;

← Data Cleaning · Next: Dashboard Design →