Net Run Rate of Every Team in the T20 League

Hard
467.

Net Run Rate of Every Team in the T20 League

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

ColumnType
match_idint
innings_noint
batting_teamvarchar
bowling_teamvarchar
runsint
ballsint
all_outbool

Primary key (together): 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).

Example

The rows must come back in the order shown.

InningsTotal

match_idinnings_nobatting_teambowling_teamrunsballsall_out
11Mumbai MonarchsChennai Cheetahs1821200
12Chennai CheetahsMumbai Monarchs1501041
21Delhi DefendersMumbai Monarchs1401200
22Mumbai MonarchsDelhi Defenders143990
31Chennai CheetahsDelhi Defenders1651181
32Delhi DefendersChennai Cheetahs1661150

Output

teamruns_forruns_againstnet_run_rate
Mumbai Monarchs3252901.654
Delhi Defenders306308-0.626
Chennai Cheetahs315348-1.01
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
innings_noint
batting_teamvarchar
bowling_teamvarchar
runsint
ballsint
all_outbool

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_idinnings_nobatting_teambowling_teamrunsballsall_out
11Mumbai MonarchsChennai Cheetahs1821200
12Chennai CheetahsMumbai Monarchs1501041
21Delhi DefendersMumbai Monarchs1401200
22Mumbai MonarchsDelhi Defenders143990
31Chennai CheetahsDelhi Defenders1651181
32Delhi DefendersChennai Cheetahs1661150

Output

teamruns_forruns_againstnet_run_rate
Mumbai Monarchs3252901.654
Delhi Defenders306308-0.626
Chennai Cheetahs315348-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.team

Another 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 →