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
| Column | Type |
|---|---|
| dismissal_id | int |
| match_id | int |
| batter | varchar |
| how_out | varchar |
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_id | match_id | batter | how_out |
|---|---|---|---|
| 1 | 801 | Rohan Iyer | c Kabir Khan b Aarav Das |
| 2 | 801 | Dev Mehta | b Farhan Patel |
| 3 | 801 | Vikram Rao | lbw b Aarav Das |
| 4 | 801 | Ishaan Bose | run out (Kabir Khan) |
| 5 | 802 | Kabir Khan | c & b Farhan Patel |
| 6 | 802 | Arjun Nair | st Rohan Iyer b Harsh Joshi |
| 7 | 802 | Neha Gupta | retired hurt |
| 8 | 802 | Rahul Singh | b Aarav Das |
Output
| bowler | wickets |
|---|---|
| Aarav Das | 3 |
| Farhan Patel | 2 |
| Harsh Joshi | 1 |
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, bowlerAnother 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, bowlerAnother 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 →