Net Run Rate of Every Team in the T20 League — SQL Problem
In a T20 league, teams level on points are separated by net run rate (NRR): the runs a team scored per over faced, minus the runs it conceded per over…
- Difficulty: Hard
- Topics: Aggregation, Subqueries, Conditional Logic
- Dialect: MySQL
- Problem: #467
Problem statement
In a T20 league, teams level on points are separated by net run rate (NRR): the runs a team scored per over faced, minus the runs it conceded per over bowled, over the whole season. Overs are balls divided by 6. One rule makes it fair: when a side is bowled out, its innings counts as the full 120 balls, both for the batting team's overs faced and for the bowling team's overs bowled.
Return every team with the columns team, runs_for, runs_against and net_run_rate = runs_for * 6 / balls_faced - runs_against * 6 / balls_bowled, rounded to 3 decimal places, using the adjusted ball counts. Order the rows by net_run_rate descending, then by team.
Tables
Table: InningsTotal
| Column | Type |
|---|---|
| match_id | int |
| innings_no | int |
| batting_team | varchar |
| bowling_team | varchar |
| runs | int |
| balls | int |
| all_out | bool |
Primary key: match_id, innings_no.
One row per completed innings; every match has two, one batted by each team. balls is the legal balls actually faced (at most 120).
Examples
Example 1
InningsTotal
| match_id | innings_no | batting_team | bowling_team | runs | balls | all_out |
|---|---|---|---|---|---|---|
| 1 | 1 | Mumbai Monarchs | Chennai Cheetahs | 182 | 120 | 0 |
| 1 | 2 | Chennai Cheetahs | Mumbai Monarchs | 150 | 104 | 1 |
| 2 | 1 | Delhi Defenders | Mumbai Monarchs | 140 | 120 | 0 |
| 2 | 2 | Mumbai Monarchs | Delhi Defenders | 143 | 99 | 0 |
| 3 | 1 | Chennai Cheetahs | Delhi Defenders | 165 | 118 | 1 |
| 3 | 2 | Delhi Defenders | Chennai Cheetahs | 166 | 115 | 0 |
Output
| team | runs_for | runs_against | net_run_rate |
|---|---|---|---|
| Mumbai Monarchs | 325 | 290 | 1.654 |
| Delhi Defenders | 306 | 308 | -0.626 |
| Chennai Cheetahs | 315 | 348 | -1.01 |
How to solve Net Run Rate of Every Team in the T20 League
Net run rate is a ratio of season totals, not an average of match run rates, so the plan is: adjust each innings, total it two ways, then divide.
The adjustment is a CASE: a bowled-out innings uses 120 balls, any other its real balls. In the example Chennai were all out in 104 balls chasing, so their 150 runs count over 20 overs — which hurts their rate — and Mumbai's bowling is credited with 20 overs too.
Each innings then feeds two teams: the batting team's runs for and balls faced, and the bowling team's runs against and balls bowled. Two grouped CTEs (by batting_team and by bowling_team) joined on the team give all four totals; the alternative unpivots each innings into a batting row and a bowling row with zeros in the other columns and groups once. Every team both bats and bowls in each of its matches, so the join loses nobody and no ball count is zero.
Finally runs_for * 6 / balls_faced - runs_against * 6 / balls_bowled, rounded to three places only at the end. Both plans scan the innings a fixed number of times.
Reference solution (MySQL)
WITH adjusted AS (
SELECT batting_team, bowling_team, runs,
CASE WHEN all_out = 1 THEN 120 ELSE balls END AS balls_used
FROM InningsTotal
), batting AS (
SELECT batting_team AS team, SUM(runs) AS runs_for, SUM(balls_used) AS balls_faced FROM adjusted GROUP BY batting_team
), bowling AS (
SELECT bowling_team AS team, SUM(runs) AS runs_against, SUM(balls_used) AS balls_bowled FROM adjusted GROUP BY bowling_team
)
SELECT b.team, b.runs_for, w.runs_against,
ROUND(b.runs_for * 6 / b.balls_faced - w.runs_against * 6 / w.balls_bowled, 3) AS net_run_rate
FROM batting b
JOIN bowling w ON w.team = b.team
ORDER BY net_run_rate DESC, b.teamAnother way
SELECT team, SUM(rf) AS runs_for, SUM(ra) AS runs_against,
ROUND(SUM(rf) * 6 / SUM(bf) - SUM(ra) * 6 / SUM(bb), 3) AS net_run_rate
FROM (
SELECT batting_team AS team, runs AS rf, IF(all_out = 1, 120, balls) AS bf, 0 AS ra, 0 AS bb FROM InningsTotal
UNION ALL
SELECT bowling_team, 0, 0, runs, IF(all_out = 1, 120, balls) FROM InningsTotal
) t
GROUP BY team
ORDER BY net_run_rate DESC, team← Longest Winning Streak of Each Football Club · The Over in Which Each T20 Innings Passed 100 Runs →