T20 Innings Scored at a Strike Rate Above 150

Easy
448.

T20 Innings Scored at a Strike Rate Above 150

EasyBasics

MySQL

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

ColumnType
innings_idint
match_idint
battervarchar
runsint
balls_facedint
dismissedbool

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

BattingInnings

innings_idmatch_idbatterrunsballs_faceddismissed
1101Rohan Iyer64311
2101Kabir Khan1281
3101Arjun Nair45300
4102Vikram Rao88521
5102Rohan Iyer21101
6102Dev Mehta30251
7103Kabir Khan52230

Output

match_idbatterrunsballs_facedstrike_rate
101Rohan Iyer6431206.45
102Vikram Rao8852169.23
102Rohan Iyer2110210
103Kabir Khan5223226.09
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.

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

ColumnType
innings_idint
match_idint
battervarchar
runsint
balls_facedint
dismissedbool

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_idmatch_idbatterrunsballs_faceddismissed
1101Rohan Iyer64311
2101Kabir Khan1281
3101Arjun Nair45300
4102Vikram Rao88521
5102Rohan Iyer21101
6102Dev Mehta30251
7103Kabir Khan52230

Output

match_idbatterrunsballs_facedstrike_rate
101Rohan Iyer6431206.45
102Vikram Rao8852169.23
102Rohan Iyer2110210
103Kabir Khan5223226.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_faced

Another 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.5

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