T20 Innings Scored at a Strike Rate Above 150 — SQL Problem
The league's broadcaster wants a graphic of the season's quickest knocks.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #448
Problem statement
The league's broadcaster wants a graphic of the season's quickest knocks. A batter's strike rate in an innings is runs scored per 100 balls faced: runs * 100 / balls_faced.
Return every innings in which the batter faced at least 10 balls and scored at a strike rate strictly greater than 150, with the columns match_id, batter, runs, balls_faced and strike_rate (rounded to 2 decimal places). An innings at exactly 150 does not qualify. Return the rows in any order.
Tables
Table: BattingInnings
| Column | Type |
|---|---|
| innings_id | int |
| match_id | int |
| batter | varchar |
| runs | int |
| balls_faced | int |
| dismissed | bool |
Primary key: innings_id.
One row per batter per match they batted in. balls_faced is always at least 1; dismissed is 1 if the batter was out.
Examples
Example 1
BattingInnings
| innings_id | match_id | batter | runs | balls_faced | dismissed |
|---|---|---|---|---|---|
| 1 | 101 | Rohan Iyer | 64 | 31 | 1 |
| 2 | 101 | Kabir Khan | 12 | 8 | 1 |
| 3 | 101 | Arjun Nair | 45 | 30 | 0 |
| 4 | 102 | Vikram Rao | 88 | 52 | 1 |
| 5 | 102 | Rohan Iyer | 21 | 10 | 1 |
| 6 | 102 | Dev Mehta | 30 | 25 | 1 |
| 7 | 103 | Kabir Khan | 52 | 23 | 0 |
Output
| match_id | batter | runs | balls_faced | strike_rate |
|---|---|---|---|---|
| 101 | Rohan Iyer | 64 | 31 | 206.45 |
| 102 | Vikram Rao | 88 | 52 | 169.23 |
| 102 | Rohan Iyer | 21 | 10 | 210 |
| 103 | Kabir Khan | 52 | 23 | 226.09 |
How to solve T20 Innings Scored at a Strike Rate Above 150
This is a filter with a calculated column. The strike rate of each innings is runs * 100 / balls_faced; MySQL's / is decimal division, so 64 runs off 31 balls is 206.45 after rounding, not an integer 206.
Two conditions decide whether an innings is shown. balls_faced >= 10 throws out cameos like 12 off 8, which would otherwise top any strike-rate chart, and the strike rate must be strictly above 150 — 21 off 10 (210) passes while 45 off 30 (exactly 150) does not. Comparing runs * 100 > 150 * balls_faced keeps the test in whole numbers, so no rounding can push a borderline innings across the bar; comparing runs / balls_faced > 1.5 is the same test, and so is filtering on the rounded column in an outer query, because a strike rate just above 150 is never close enough to round down to it.
Round only the value you display. The query is a single scan of the table.
Reference solution (MySQL)
SELECT match_id, batter, runs, balls_faced,
ROUND(runs * 100 / balls_faced, 2) AS strike_rate
FROM BattingInnings
WHERE balls_faced >= 10
AND runs * 100 > 150 * balls_facedAnother way
SELECT match_id, batter, runs, balls_faced, ROUND(100 * runs / balls_faced, 2) AS strike_rate FROM BattingInnings WHERE balls_faced >= 10 AND runs / balls_faced > 1.5Another way
SELECT * FROM (SELECT match_id, batter, runs, balls_faced, ROUND(runs * 100 / balls_faced, 2) AS strike_rate FROM BattingInnings WHERE NOT balls_faced < 10) t WHERE strike_rate > 150← App Sessions From the Event Stream · Economical Bowlers of the T20 Season →