Economical Bowlers of the T20 Season — SQL Problem
Selectors are shortlisting bowlers who kept the scoring down across the season.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #449
Problem statement
Selectors are shortlisting bowlers who kept the scoring down across the season. Every spell in BowlingSpell is recorded in complete overs, so a bowler's season economy is their total runs conceded divided by their total overs.
Return each bowler who bowled at least 10 overs in total and whose season economy is strictly below 7, with the columns bowler, overs, runs_conceded and economy (rounded to 2 decimal places). Order the rows by economy ascending, then by bowler alphabetically.
Tables
Table: BowlingSpell
| Column | Type |
|---|---|
| spell_id | int |
| match_id | int |
| bowler | varchar |
| overs | int |
| runs_conceded | int |
| wickets | int |
Primary key: spell_id.
One row per bowler per match. overs is a whole number between 1 and 4 (a T20 bowler may bowl at most four).
Examples
Example 1
BowlingSpell
| spell_id | match_id | bowler | overs | runs_conceded | wickets |
|---|---|---|---|---|---|
| 1 | 201 | Aarav Das | 4 | 22 | 2 |
| 2 | 202 | Aarav Das | 4 | 31 | 1 |
| 3 | 203 | Aarav Das | 3 | 18 | 0 |
| 4 | 201 | Farhan Patel | 4 | 26 | 3 |
| 5 | 202 | Farhan Patel | 4 | 30 | 1 |
| 6 | 203 | Farhan Patel | 2 | 14 | 0 |
| 7 | 201 | Ishaan Bose | 4 | 20 | 1 |
| 8 | 202 | Ishaan Bose | 3 | 19 | 2 |
| 9 | 203 | Harsh Joshi | 4 | 41 | 0 |
Output
| bowler | overs | runs_conceded | economy |
|---|---|---|---|
| Aarav Das | 11 | 71 | 6.45 |
How to solve Economical Bowlers of the T20 Season
The question is about each bowler's whole season, so the spells are grouped by bowler and the runs and overs are summed. The season economy is SUM(runs_conceded) / SUM(overs) — a ratio of totals. Averaging the per-spell economies instead would be wrong: a single expensive over would count as much as a tidy four-over spell.
Both conditions are about the totals, so they go in HAVING: at least 10 overs, and an economy strictly below 7. Writing the second as SUM(runs_conceded) < 7 * SUM(overs) keeps it in whole numbers, so a bowler at exactly 7.00 is reliably left out. The derived-table version computes the totals first and filters them in an outer query, which reads the same.
The order is by the rounded economy and then by name, so two bowlers with the same figure are listed alphabetically. The query is one scan and one grouping of the spells.
Reference solution (MySQL)
SELECT bowler,
SUM(overs) AS overs,
SUM(runs_conceded) AS runs_conceded,
ROUND(SUM(runs_conceded) / SUM(overs), 2) AS economy
FROM BowlingSpell
GROUP BY bowler
HAVING SUM(overs) >= 10 AND SUM(runs_conceded) < 7 * SUM(overs)
ORDER BY economy, bowlerAnother way
SELECT bowler, overs, runs_conceded, ROUND(runs_conceded / overs, 2) AS economy FROM (SELECT bowler, SUM(overs) AS overs, SUM(runs_conceded) AS runs_conceded FROM BowlingSpell GROUP BY bowler) t WHERE overs >= 10 AND runs_conceded / overs < 7 ORDER BY economy, bowler← T20 Innings Scored at a Strike Rate Above 150 · Full-Time Results Board for the Football League →