Death-Overs Economy From Ball-by-Ball Data

Medium
459.

Death-Overs Economy From Ball-by-Ball Data

The analytics team rates bowlers on the death overs, overs 17 to 20 of an innings. From the ball-by-ball feed, a bowler is charged with the runs off the bat plus the extra runs of wides and no-balls; byes and leg-byes are not charged to the bowler. Wides and no-balls are not legal deliveries and do not count as balls bowled; every other delivery does.

Return each bowler with at least 12 legal deliveries in overs 17–20, with the columns bowler, legal_balls, runs_conceded and economy = runs_conceded * 6 / legal_balls, rounded to 2 decimal places. Order the rows by economy ascending, then by bowler.

Tables

Table: Delivery

ColumnTypeValues
delivery_idint
match_idint
over_noint
bowlervarchar
batter_runsint
extra_runsint
extra_typeenumnone, wide, noball, bye, legbye

Primary key: delivery_id.

One row per delivery. over_no is 1–20. extra_runs is 0 when extra_type is none.

Example

The rows must come back in the order shown.

Delivery

delivery_idmatch_idover_nobowlerbatter_runsextra_runsextra_type
160118Aarav Das10none
260118Aarav Das10none
360118Aarav Das10none
460118Aarav Das40none
560118Aarav Das10none
660118Aarav Das10none
760118Aarav Das10none
860118Aarav Das40none
960118Aarav Das10none
1060118Aarav Das10none
24 more rows

Output

bowlerlegal_ballsruns_concededeconomy
Aarav Das132210.15
Farhan Patel122311.5
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.

Death-Overs Economy From Ball-by-Ball Data — SQL Problem

The analytics team rates bowlers on the death overs, overs 17 to 20 of an innings.

  • Difficulty: Medium
  • Topics: Conditional Logic, Aggregation
  • Dialect: MySQL
  • Problem: #459

Problem statement

The analytics team rates bowlers on the death overs, overs 17 to 20 of an innings. From the ball-by-ball feed, a bowler is charged with the runs off the bat plus the extra runs of wides and no-balls; byes and leg-byes are not charged to the bowler. Wides and no-balls are not legal deliveries and do not count as balls bowled; every other delivery does.

Return each bowler with at least 12 legal deliveries in overs 17–20, with the columns bowler, legal_balls, runs_conceded and economy = runs_conceded * 6 / legal_balls, rounded to 2 decimal places. Order the rows by economy ascending, then by bowler.

Tables

Table: Delivery

ColumnType
delivery_idint
match_idint
over_noint
bowlervarchar
batter_runsint
extra_runsint
extra_typeenum(none, wide, noball, bye, legbye)

Primary key: delivery_id.

One row per delivery. over_no is 1–20. extra_runs is 0 when extra_type is none.

Examples

Example 1

Delivery

delivery_idmatch_idover_nobowlerbatter_runsextra_runsextra_type
160118Aarav Das10none
260118Aarav Das10none
360118Aarav Das10none
460118Aarav Das40none
560118Aarav Das10none
660118Aarav Das10none
760118Aarav Das10none
860118Aarav Das40none
960118Aarav Das10none
1060118Aarav Das10none
1160118Aarav Das10none
1260118Aarav Das40none
1360118Aarav Das01wide
1460119Aarav Das04legbye
1560119Farhan Patel00none
1660119Farhan Patel20none
1760119Farhan Patel20none
1860119Farhan Patel00none
1960119Farhan Patel20none
2060119Farhan Patel20none
2160119Farhan Patel00none
2260119Farhan Patel20none
2360119Farhan Patel20none
2460119Farhan Patel00none
2560119Farhan Patel20none
2660119Farhan Patel20none
2760120Farhan Patel61noball
286015Farhan Patel40none
2960117Ishaan Bose10none
3060117Ishaan Bose10none
3160117Ishaan Bose10none
3260117Ishaan Bose10none
3360117Ishaan Bose10none
3460117Ishaan Bose10none

Output

bowlerlegal_ballsruns_concededeconomy
Aarav Das132210.15
Farhan Patel122311.5

How to solve Death-Overs Economy From Ball-by-Ball Data

Every delivery contributes two conditional amounts, and the rest is grouping. A delivery is a legal ball unless it is a wide or a no-ball — CASE WHEN extra_type IN ('wide', 'noball') THEN 0 ELSE 1 END. Its charged runs are always the runs off the bat, plus the extras only for wides and no-balls; byes and leg-byes are legal balls, but their runs go to the batting side's extras, not the bowler. Summing the two expressions per bowler, after filtering to over_no BETWEEN 17 AND 20, gives the death-overs totals.

The minimum is on legal balls, so it is a HAVING on the same sum. Economy is runs per six legal balls — runs * 6 / balls — rounded only for display. A bowler with all their balls before over 17 has no rows after the filter and never appears, so there is no division by zero.

The alternative computes the two per-ball values once in a CTE with IF and aggregates them, which avoids repeating the CASE. One scan of the deliveries.

Reference solution (MySQL)

SELECT bowler,
       SUM(CASE WHEN extra_type IN ('wide', 'noball') THEN 0 ELSE 1 END) AS legal_balls,
       SUM(batter_runs + CASE WHEN extra_type IN ('wide', 'noball') THEN extra_runs ELSE 0 END) AS runs_conceded,
       ROUND(SUM(batter_runs + CASE WHEN extra_type IN ('wide', 'noball') THEN extra_runs ELSE 0 END) * 6
             / SUM(CASE WHEN extra_type IN ('wide', 'noball') THEN 0 ELSE 1 END), 2) AS economy
FROM Delivery
WHERE over_no BETWEEN 17 AND 20
GROUP BY bowler
HAVING SUM(CASE WHEN extra_type IN ('wide', 'noball') THEN 0 ELSE 1 END) >= 12
ORDER BY economy, bowler

Another way

WITH death AS (
  SELECT bowler,
         IF(extra_type = 'wide' OR extra_type = 'noball', 0, 1) AS legal,
         batter_runs + IF(extra_type = 'wide' OR extra_type = 'noball', extra_runs, 0) AS charged
  FROM Delivery WHERE over_no >= 17
)
SELECT bowler, SUM(legal) AS legal_balls, SUM(charged) AS runs_conceded, ROUND(SUM(charged) * 6 / SUM(legal), 2) AS economy
FROM death GROUP BY bowler HAVING SUM(legal) >= 12
ORDER BY economy, bowler

← Best Bowling Figures of Each Bowler This Season · Fantasy Gameweek Scores With the Captain's Double Points →