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
| Column | Type |
|---|---|
| delivery_id | int |
| match_id | int |
| over_no | int |
| bowler | varchar |
| batter_runs | int |
| extra_runs | int |
| extra_type | enum(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_id | match_id | over_no | bowler | batter_runs | extra_runs | extra_type |
|---|---|---|---|---|---|---|
| 1 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 2 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 3 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 4 | 601 | 18 | Aarav Das | 4 | 0 | none |
| 5 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 6 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 7 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 8 | 601 | 18 | Aarav Das | 4 | 0 | none |
| 9 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 10 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 11 | 601 | 18 | Aarav Das | 1 | 0 | none |
| 12 | 601 | 18 | Aarav Das | 4 | 0 | none |
| 13 | 601 | 18 | Aarav Das | 0 | 1 | wide |
| 14 | 601 | 19 | Aarav Das | 0 | 4 | legbye |
| 15 | 601 | 19 | Farhan Patel | 0 | 0 | none |
| 16 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 17 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 18 | 601 | 19 | Farhan Patel | 0 | 0 | none |
| 19 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 20 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 21 | 601 | 19 | Farhan Patel | 0 | 0 | none |
| 22 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 23 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 24 | 601 | 19 | Farhan Patel | 0 | 0 | none |
| 25 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 26 | 601 | 19 | Farhan Patel | 2 | 0 | none |
| 27 | 601 | 20 | Farhan Patel | 6 | 1 | noball |
| 28 | 601 | 5 | Farhan Patel | 4 | 0 | none |
| 29 | 601 | 17 | Ishaan Bose | 1 | 0 | none |
| 30 | 601 | 17 | Ishaan Bose | 1 | 0 | none |
| 31 | 601 | 17 | Ishaan Bose | 1 | 0 | none |
| 32 | 601 | 17 | Ishaan Bose | 1 | 0 | none |
| 33 | 601 | 17 | Ishaan Bose | 1 | 0 | none |
| 34 | 601 | 17 | Ishaan Bose | 1 | 0 | none |
Output
| bowler | legal_balls | runs_conceded | economy |
|---|---|---|---|
| Aarav Das | 13 | 22 | 10.15 |
| Farhan Patel | 12 | 23 | 11.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, bowlerAnother 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 →