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
| Column | Type |
|---|---|
| match_id | int |
| match_date | date |
| batter | varchar |
| runs | int |
Primary key: match_id, batter.
One row per batter per match they batted in; a match has one date.
Examples
Example 1
BatterMatchRuns
| match_id | match_date | batter | runs |
|---|---|---|---|
| 1 | 2024-03-22 | Rohan Iyer | 72 |
| 1 | 2024-03-22 | Kabir Khan | 45 |
| 2 | 2024-03-23 | Arjun Nair | 88 |
| 2 | 2024-03-23 | Dev Mehta | 12 |
| 3 | 2024-03-25 | Kabir Khan | 27 |
| 3 | 2024-03-25 | Dev Mehta | 64 |
| 4 | 2024-03-25 | Rohan Iyer | 16 |
| 5 | 2024-03-27 | Arjun Nair | 0 |
| 5 | 2024-03-27 | Kabir Khan | 4 |
Output
| match_date | cap_holder | season_runs |
|---|---|---|
| 2024-03-22 | Rohan Iyer | 72 |
| 2024-03-23 | Arjun Nair | 88 |
| 2024-03-25 | Arjun Nair | 88 |
| 2024-03-27 | Arjun Nair | 88 |
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_dateAnother 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 →