Economical Bowlers of the T20 Season

Easy
449.

Economical Bowlers of the T20 Season

EasyAggregation

MySQL

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

ColumnType
spell_idint
match_idint
bowlervarchar
oversint
runs_concededint
wicketsint

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).

Example

The rows must come back in the order shown.

BowlingSpell

spell_idmatch_idbowleroversruns_concededwickets
1201Aarav Das4222
2202Aarav Das4311
3203Aarav Das3180
4201Farhan Patel4263
5202Farhan Patel4301
6203Farhan Patel2140
7201Ishaan Bose4201
8202Ishaan Bose3192
9203Harsh Joshi4410

Output

bowleroversruns_concededeconomy
Aarav Das11716.45
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.

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

ColumnType
spell_idint
match_idint
bowlervarchar
oversint
runs_concededint
wicketsint

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_idmatch_idbowleroversruns_concededwickets
1201Aarav Das4222
2202Aarav Das4311
3203Aarav Das3180
4201Farhan Patel4263
5202Farhan Patel4301
6203Farhan Patel2140
7201Ishaan Bose4201
8202Ishaan Bose3192
9203Harsh Joshi4410

Output

bowleroversruns_concededeconomy
Aarav Das11716.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, bowler

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