Wickets Credited to Each Bowler From the Dismissal Text

Medium
464.

Wickets Credited to Each Bowler From the Dismissal Text

An old scoring app stored only the scorecard's dismissal text, in the usual notation: b Aarav Das (bowled), lbw b Aarav Das, c Rohan Iyer b Aarav Das (caught), c & b Aarav Das (caught by the bowler), st Kabir Khan b Aarav Das (stumped), run out (Dev Mehta) and retired hurt. The bowler is credited with the wicket in every form that names one after b ; run outs and retirements credit no bowler.

Return the columns bowler and wickets (dismissals credited to them). Order the rows by wickets descending, then by bowler.

Tables

Table: DismissalLog

ColumnType
dismissal_idint
match_idint
battervarchar
how_outvarchar

Primary key: dismissal_id.

One row per dismissal, how_out in the notation above. Player names are first name and surname, each starting with a capital letter.

Example

The rows must come back in the order shown.

DismissalLog

dismissal_idmatch_idbatterhow_out
1801Rohan Iyerc Kabir Khan b Aarav Das
2801Dev Mehtab Farhan Patel
3801Vikram Raolbw b Aarav Das
4801Ishaan Boserun out (Kabir Khan)
5802Kabir Khanc & b Farhan Patel
6802Arjun Nairst Rohan Iyer b Harsh Joshi
7802Neha Guptaretired hurt
8802Rahul Singhb Aarav Das

Output

bowlerwickets
Aarav Das3
Farhan Patel2
Harsh Joshi1
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.

Wickets Credited to Each Bowler From the Dismissal Text

An old scoring app stored only the scorecard's dismissal text, in the usual notation: b Aarav Das (bowled), lbw b Aarav Das, c Rohan Iyer b Aarav Das…

  • Difficulty: Medium
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #464

Problem statement

An old scoring app stored only the scorecard's dismissal text, in the usual notation: b Aarav Das (bowled), lbw b Aarav Das, c Rohan Iyer b Aarav Das (caught), c & b Aarav Das (caught by the bowler), st Kabir Khan b Aarav Das (stumped), run out (Dev Mehta) and retired hurt. The bowler is credited with the wicket in every form that names one after b ; run outs and retirements credit no bowler.

Return the columns bowler and wickets (dismissals credited to them). Order the rows by wickets descending, then by bowler.

Tables

Table: DismissalLog

ColumnType
dismissal_idint
match_idint
battervarchar
how_outvarchar

Primary key: dismissal_id.

One row per dismissal, how_out in the notation above. Player names are first name and surname, each starting with a capital letter.

Examples

Example 1

DismissalLog

dismissal_idmatch_idbatterhow_out
1801Rohan Iyerc Kabir Khan b Aarav Das
2801Dev Mehtab Farhan Patel
3801Vikram Raolbw b Aarav Das
4801Ishaan Boserun out (Kabir Khan)
5802Kabir Khanc & b Farhan Patel
6802Arjun Nairst Rohan Iyer b Harsh Joshi
7802Neha Guptaretired hurt
8802Rahul Singhb Aarav Das

Output

bowlerwickets
Aarav Das3
Farhan Patel2
Harsh Joshi1

How to solve Wickets Credited to Each Bowler From the Dismissal Text

The work is extracting a name from free text and then counting. In the scorecard notation the bowler always comes last, after a b marker, so the name is everything after the last marker. SUBSTRING_INDEX(how_out, ' b ', -1) returns exactly that for lbw b …, c … b …, c & b … and st … b …. Bowled alone, b Farhan Patel, starts with the marker and has no space before it, so it needs its own branch (SUBSTRING(how_out, 3)) — or put a space in front of every text with CONCAT(' ', how_out) so that one rule covers all forms, as the first alternative does.

Run outs and retired hurt match neither branch; the CASE gives NULL and the outer filter drops them. Grouping on the extracted name and counting gives each bowler's wickets. Names here never contain a lone lower-case b, which is what makes the marker unambiguous — real data would need a stricter parse. One scan, one grouping.

Reference solution (MySQL)

SELECT bowler, COUNT(*) AS wickets
FROM (
  SELECT CASE
           WHEN how_out LIKE 'b %' THEN SUBSTRING(how_out, 3)
           WHEN how_out LIKE '% b %' THEN SUBSTRING_INDEX(how_out, ' b ', -1)
         END AS bowler
  FROM DismissalLog
) credited
WHERE bowler IS NOT NULL
GROUP BY bowler
ORDER BY wickets DESC, bowler

Another way

SELECT SUBSTRING_INDEX(CONCAT(' ', how_out), ' b ', -1) AS bowler, COUNT(*) AS wickets FROM DismissalLog WHERE how_out NOT LIKE 'run out%' AND how_out <> 'retired hurt' GROUP BY SUBSTRING_INDEX(CONCAT(' ', how_out), ' b ', -1) ORDER BY wickets DESC, bowler

Another way

SELECT bowler, COUNT(*) AS wickets FROM (SELECT SUBSTRING(how_out, 3) AS bowler FROM DismissalLog WHERE how_out LIKE 'b %' UNION ALL SELECT SUBSTRING_INDEX(how_out, ' b ', -1) FROM DismissalLog WHERE how_out LIKE '% b %') x GROUP BY bowler ORDER BY wickets DESC, bowler

← Monthly Fantasy Contest Entries and the Change From Last Month · Goalkeepers' Clean-Sheet Percentage →