Best Bowling Figures of Each Bowler This Season

Medium
458.

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

ColumnType
spell_idint
match_idint
bowlervarchar
wicketsint
runs_concededint

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

ColumnType
match_idint
match_datedate
venuevarchar

Primary key: match_id.

One row per match; no two matches share a date.

Example

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

Spell

spell_idmatch_idbowlerwicketsruns_conceded
11Aarav Das328
22Aarav Das431
33Aarav Das423
41Farhan Patel218
53Farhan Patel218
62Ishaan Bose040
74Ishaan Bose135

LeagueGame

match_idmatch_datevenue
12024-04-02Riverside Oval
22024-04-06Fort Ground
32024-04-11Coastal Arena
42024-04-15Hillside Park

Output

bowlerbest_figuresmatch_date
Aarav Das4/232024-04-11
Farhan Patel2/182024-04-02
Ishaan Bose1/352024-04-15
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.

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

ColumnType
spell_idint
match_idint
bowlervarchar
wicketsint
runs_concededint

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

ColumnType
match_idint
match_datedate
venuevarchar

Primary key: match_id.

One row per match; no two matches share a date.

Examples

Example 1

Spell

spell_idmatch_idbowlerwicketsruns_conceded
11Aarav Das328
22Aarav Das431
33Aarav Das423
41Farhan Patel218
53Farhan Patel218
62Ishaan Bose040
74Ishaan Bose135

LeagueGame

match_idmatch_datevenue
12024-04-02Riverside Oval
22024-04-06Fort Ground
32024-04-11Coastal Arena
42024-04-15Hillside Park

Output

bowlerbest_figuresmatch_date
Aarav Das4/232024-04-11
Farhan Patel2/182024-04-02
Ishaan Bose1/352024-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 = 1

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