Orange Cap Leader After Every Match Day

Hard
472.

Orange Cap Leader After Every Match Day

In the T20 league the orange cap is worn by the season's leading run-scorer. After every match day (each distinct match_date in the table), the leader is the batter with the highest total runs on or before that date — counting batters who did not play that day. If several batters share the top total, the cap goes to the one whose name comes first alphabetically.

Return one row per match day with the columns match_date, cap_holder and season_runs (the holder's total after that day). Order the rows by match_date.

Tables

Table: BatterMatchRuns

ColumnType
match_idint
match_datedate
battervarchar
runsint

Primary key (together): match_id, batter.

One row per batter per match they batted in; a match has one date.

Example

The rows must come back in the order shown.

BatterMatchRuns

match_idmatch_datebatterruns
12024-03-22Rohan Iyer72
12024-03-22Kabir Khan45
22024-03-23Arjun Nair88
22024-03-23Dev Mehta12
32024-03-25Kabir Khan27
32024-03-25Dev Mehta64
42024-03-25Rohan Iyer16
52024-03-27Arjun Nair0
52024-03-27Kabir Khan4

Output

match_datecap_holderseason_runs
2024-03-22Rohan Iyer72
2024-03-23Arjun Nair88
2024-03-25Arjun Nair88
2024-03-27Arjun Nair88
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.

Orange Cap Leader After Every Match Day — SQL Problem

In the T20 league the orange cap is worn by the season's leading run-scorer.

  • Difficulty: Hard
  • Topics: Window Functions, Subqueries, Joins
  • Dialect: MySQL
  • Problem: #472

Problem statement

In the T20 league the orange cap is worn by the season's leading run-scorer. After every match day (each distinct match_date in the table), the leader is the batter with the highest total runs on or before that date — counting batters who did not play that day. If several batters share the top total, the cap goes to the one whose name comes first alphabetically.

Return one row per match day with the columns match_date, cap_holder and season_runs (the holder's total after that day). Order the rows by match_date.

Tables

Table: BatterMatchRuns

ColumnType
match_idint
match_datedate
battervarchar
runsint

Primary key: match_id, batter.

One row per batter per match they batted in; a match has one date.

Examples

Example 1

BatterMatchRuns

match_idmatch_datebatterruns
12024-03-22Rohan Iyer72
12024-03-22Kabir Khan45
22024-03-23Arjun Nair88
22024-03-23Dev Mehta12
32024-03-25Kabir Khan27
32024-03-25Dev Mehta64
42024-03-25Rohan Iyer16
52024-03-27Arjun Nair0
52024-03-27Kabir Khan4

Output

match_datecap_holderseason_runs
2024-03-22Rohan Iyer72
2024-03-23Arjun Nair88
2024-03-25Arjun Nair88
2024-03-27Arjun Nair88

How to solve Orange Cap Leader After Every Match Day

A running total per batter alone is not enough: the leader after 25 March must consider Arjun, who did not bat that day but leads on his 88 from the 23rd. What is needed is every batter's total as of every match day.

So build the grid explicitly. Take the distinct match days and join each to all innings on or before it — r.match_date <= d.match_date, a non-equi join. Grouping by (day, batter) and summing gives each batter's season total as it stood after that day; a batter who had not batted yet simply has no row for the earlier days. Then rank batters within each day by total descending and name ascending with ROW_NUMBER(), and keep the first. The name is the explicit tie-break, so exactly one holder per day — on 25 March Rohan (88) and Arjun (88) tie and Arjun takes the cap.

The alternative keeps a total when no other batter on the same day has more runs, or the same runs and an earlier name. The join is days × innings, fine at league scale; a running-sum window per batter plus a carry-forward would avoid it on large data.

Reference solution (MySQL)

WITH match_days AS (
  SELECT DISTINCT match_date FROM BatterMatchRuns
), totals AS (
  SELECT d.match_date, r.batter, SUM(r.runs) AS season_runs
  FROM match_days d
  JOIN BatterMatchRuns r ON r.match_date <= d.match_date
  GROUP BY d.match_date, r.batter
), ranked AS (
  SELECT match_date, batter, season_runs,
         ROW_NUMBER() OVER (PARTITION BY match_date ORDER BY season_runs DESC, batter) AS rn
  FROM totals
)
SELECT match_date, batter AS cap_holder, season_runs
FROM ranked
WHERE rn = 1
ORDER BY match_date

Another way

WITH totals AS (
  SELECT d.match_date, r.batter, SUM(r.runs) AS season_runs
  FROM (SELECT DISTINCT match_date FROM BatterMatchRuns) d
  JOIN BatterMatchRuns r ON r.match_date <= d.match_date
  GROUP BY d.match_date, r.batter
)
SELECT t.match_date, t.batter AS cap_holder, t.season_runs
FROM totals t
WHERE NOT EXISTS (
  SELECT 1 FROM totals u
  WHERE u.match_date = t.match_date
    AND (u.season_runs > t.season_runs OR (u.season_runs = t.season_runs AND u.batter < t.batter))
)
ORDER BY t.match_date

← Days Each Player Was Out Injured, Merging Overlapping Records · New Kids-Safe Originals for the Family Row →