Days Each Player Was Out Injured, Merging Overlapping Records

Hard
471.

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

ColumnType
record_idint
player_namevarchar
injuryvarchar
out_fromdate
out_untildate

Primary key: record_id.

out_until is never before out_from; a one-day record has them equal.

Example

The rows must come back in the order shown.

InjuryRecord

record_idplayer_nameinjuryout_fromout_until
1Rohan IyerHamstring2024-09-022024-09-15
2Rohan IyerHamstring2024-09-052024-09-20
3Rohan IyerAnkle2024-10-012024-10-03
4Kabir KhanConcussion2024-09-102024-09-16
5Kabir KhanGroin2024-09-172024-09-21
6Dev MehtaKnee2024-08-202024-09-08
7Dev MehtaKnee2024-08-252024-08-30

Output

player_namelayoffsdays_out
Rohan Iyer222
Dev Mehta120
Kabir Khan112
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.

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

ColumnType
record_idint
player_namevarchar
injuryvarchar
out_fromdate
out_untildate

Primary key: record_id.

out_until is never before out_from; a one-day record has them equal.

Examples

Example 1

InjuryRecord

record_idplayer_nameinjuryout_fromout_until
1Rohan IyerHamstring2024-09-022024-09-15
2Rohan IyerHamstring2024-09-052024-09-20
3Rohan IyerAnkle2024-10-012024-10-03
4Kabir KhanConcussion2024-09-102024-09-16
5Kabir KhanGroin2024-09-172024-09-21
6Dev MehtaKnee2024-08-202024-09-08
7Dev MehtaKnee2024-08-252024-08-30

Output

player_namelayoffsdays_out
Rohan Iyer222
Dev Mehta120
Kabir Khan112

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_name

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