Best Bowling Figures of Each Bowler This Season
In cricket a bowler's best figures are the spell with the most wickets; among spells with equal wickets, the one with the fewest runs conceded is better.
- Difficulty: Medium
- Topics: Window Functions, Strings, Joins
- Dialect: MySQL
- Problem: #458
Problem statement
In cricket a bowler's best figures are the spell with the most wickets; among spells with equal wickets, the one with the fewest runs conceded is better. If two spells are identical, the earlier match counts as the best.
Return one row per bowler with the columns bowler, best_figures (written wickets/runs, for example 4/23) and match_date (the date of the match of that spell). Return the rows in any order.
Tables
Table: Spell
| Column | Type |
|---|---|
| spell_id | int |
| match_id | int |
| bowler | varchar |
| wickets | int |
| runs_conceded | int |
Primary key: spell_id.
One row per bowler per match they bowled in; a bowler bowls at most one spell in a match. match_id is always in LeagueGame.
Table: LeagueGame
| Column | Type |
|---|---|
| match_id | int |
| match_date | date |
| venue | varchar |
Primary key: match_id.
One row per match; no two matches share a date.
Examples
Example 1
Spell
| spell_id | match_id | bowler | wickets | runs_conceded |
|---|---|---|---|---|
| 1 | 1 | Aarav Das | 3 | 28 |
| 2 | 2 | Aarav Das | 4 | 31 |
| 3 | 3 | Aarav Das | 4 | 23 |
| 4 | 1 | Farhan Patel | 2 | 18 |
| 5 | 3 | Farhan Patel | 2 | 18 |
| 6 | 2 | Ishaan Bose | 0 | 40 |
| 7 | 4 | Ishaan Bose | 1 | 35 |
LeagueGame
| match_id | match_date | venue |
|---|---|---|
| 1 | 2024-04-02 | Riverside Oval |
| 2 | 2024-04-06 | Fort Ground |
| 3 | 2024-04-11 | Coastal Arena |
| 4 | 2024-04-15 | Hillside Park |
Output
| bowler | best_figures | match_date |
|---|---|---|
| Aarav Das | 4/23 | 2024-04-11 |
| Farhan Patel | 2/18 | 2024-04-02 |
| Ishaan Bose | 1/35 | 2024-04-15 |
How to solve Best Bowling Figures of Each Bowler This Season
Best figures are defined by an ordering, so the natural tool is a ranking window. First join Spell to LeagueGame to bring in each spell's date. Then ROW_NUMBER() OVER (PARTITION BY bowler ORDER BY wickets DESC, runs_conceded, match_date) numbers each bowler's spells from best to worst: more wickets first, fewer runs among equal wickets, the earlier match among identical spells. The statement's rules make that order total — a bowler has at most one spell per match and matches have distinct dates — so ROW_NUMBER is safe and exactly one row per bowler gets 1.
The display string is CONCAT(wickets, '/', runs_conceded), where CONCAT converts the numbers to text.
The NOT EXISTS alternative says the same as a comparison: keep a spell when no other spell of the bowler beats it on wickets, or ties on wickets and beats it on runs, or ties on both and came earlier. It is easy to get the OR-chain wrong; the window states the order once. The window plan is one sort per bowler.
Reference solution (MySQL)
SELECT bowler, CONCAT(wickets, '/', runs_conceded) AS best_figures, match_date
FROM (
SELECT s.bowler, s.wickets, s.runs_conceded, g.match_date,
ROW_NUMBER() OVER (PARTITION BY s.bowler ORDER BY s.wickets DESC, s.runs_conceded, g.match_date) AS rn
FROM Spell s
JOIN LeagueGame g ON g.match_id = s.match_id
) ranked
WHERE rn = 1Another way
SELECT s.bowler, CONCAT(s.wickets, '/', s.runs_conceded) AS best_figures, g.match_date
FROM Spell s JOIN LeagueGame g ON g.match_id = s.match_id
WHERE NOT EXISTS (
SELECT 1 FROM Spell t JOIN LeagueGame h ON h.match_id = t.match_id
WHERE t.bowler = s.bowler
AND (t.wickets > s.wickets
OR (t.wickets = s.wickets AND t.runs_conceded < s.runs_conceded)
OR (t.wickets = s.wickets AND t.runs_conceded = s.runs_conceded AND h.match_date < g.match_date))
)← Highest Batting Partnership in Each T20 Match · Death-Overs Economy From Ball-by-Ball Data →