Days Each Player Was Out Injured, Merging Overlapping Records
The football club's physio and its doctor both log injuries, so one layoff is often recorded twice with different dates, and a player can pick up a new…
- Difficulty: Hard
- Topics: Dates, Window Functions
- Dialect: MySQL
- Problem: #471
Problem statement
The football club's physio and its doctor both log injuries, so one layoff is often recorded twice with different dates, and a player can pick up a new knock before the last one clears. Each record covers the days from out_from to out_until, both inclusive. Records that overlap or touch (one ends the day before the next starts) belong to one continuous layoff.
Return every player who has a record, with the columns player_name, layoffs (continuous layoffs after merging) and days_out (distinct days unavailable). Order the rows by days_out descending, then by player_name.
Tables
Table: InjuryRecord
| Column | Type |
|---|---|
| record_id | int |
| player_name | varchar |
| injury | varchar |
| out_from | date |
| out_until | date |
Primary key: record_id.
out_until is never before out_from; a one-day record has them equal.
Examples
Example 1
InjuryRecord
| record_id | player_name | injury | out_from | out_until |
|---|---|---|---|---|
| 1 | Rohan Iyer | Hamstring | 2024-09-02 | 2024-09-15 |
| 2 | Rohan Iyer | Hamstring | 2024-09-05 | 2024-09-20 |
| 3 | Rohan Iyer | Ankle | 2024-10-01 | 2024-10-03 |
| 4 | Kabir Khan | Concussion | 2024-09-10 | 2024-09-16 |
| 5 | Kabir Khan | Groin | 2024-09-17 | 2024-09-21 |
| 6 | Dev Mehta | Knee | 2024-08-20 | 2024-09-08 |
| 7 | Dev Mehta | Knee | 2024-08-25 | 2024-08-30 |
Output
| player_name | layoffs | days_out |
|---|---|---|
| Rohan Iyer | 2 | 22 |
| Dev Mehta | 1 | 20 |
| Kabir Khan | 1 | 12 |
How to solve Days Each Player Was Out Injured, Merging Overlapping Records
This is interval merging, a gaps-and-islands problem on ranges. Sort each player's records by out_from. Walking down the list, a record belongs to the current layoff if it starts no later than the day after the furthest out_until seen so far; otherwise it opens a new one. "Furthest so far" must be a running maximum over all earlier rows — MAX(out_until) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) — because a long record (Dev's 20 Aug–8 Sep) can swallow a later short one, and the next record must be compared with the long one's end, not the short one's.
The flag is 1 for the first record of a player (no earlier rows, reach NULL) or when out_from > reach + 1 day; touching records (Kabir's 16th and 17th) merge. A running SUM of the flags numbers the layoffs, and grouping by that number gives each layoff's first and last day. Inclusive ends mean a layoff lasts DATEDIFF(last, first) + 1 days, and merged layoffs never overlap, so their lengths add up to the distinct days.
The alternative expands every record into its days with a recursive CTE (records are short), counts distinct days, and counts a layoff at every day whose previous day is not out. It is simpler to trust but proportional to the number of days; the window plan is two sorts per player.
Reference solution (MySQL)
WITH ordered AS (
SELECT player_name, record_id, out_from, out_until,
MAX(out_until) OVER (PARTITION BY player_name ORDER BY out_from, out_until, record_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS reach
FROM InjuryRecord
), flagged AS (
SELECT player_name, record_id, out_from, out_until,
CASE WHEN reach IS NULL OR out_from > DATE_ADD(reach, INTERVAL 1 DAY) THEN 1 ELSE 0 END AS starts_new
FROM ordered
), numbered AS (
SELECT player_name, out_from, out_until,
SUM(starts_new) OVER (PARTITION BY player_name ORDER BY out_from, out_until, record_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS layoff_no
FROM flagged
), merged AS (
SELECT player_name, layoff_no, MIN(out_from) AS first_day, MAX(out_until) AS last_day
FROM numbered
GROUP BY player_name, layoff_no
)
SELECT player_name, COUNT(*) AS layoffs, SUM(DATEDIFF(last_day, first_day) + 1) AS days_out
FROM merged
GROUP BY player_name
ORDER BY days_out DESC, player_nameAnother way
WITH RECURSIVE days AS (
SELECT player_name, out_from AS d, out_until FROM InjuryRecord
UNION ALL
SELECT player_name, DATE_ADD(d, INTERVAL 1 DAY), out_until FROM days WHERE d < out_until
), distinct_days AS (
SELECT DISTINCT player_name, d FROM days
)
SELECT a.player_name,
SUM(CASE WHEN NOT EXISTS (SELECT 1 FROM distinct_days b WHERE b.player_name = a.player_name AND b.d = DATE_SUB(a.d, INTERVAL 1 DAY)) THEN 1 ELSE 0 END) AS layoffs,
COUNT(*) AS days_out
FROM distinct_days a
GROUP BY a.player_name
ORDER BY days_out DESC, a.player_name← Fantasy App Signup Cohorts Retained Into the Next Month · Orange Cap Leader After Every Match Day →