Gaps in the On-Call Rota

Hard
344.

Gaps in the On-Call Rota

Each team must have someone on call every day. A shift covers every date from start_date to end_date, both inclusive; shifts may overlap and may start before or end after the period. The audit covers 2025-03-01 to 2025-03-14.

Report every gap — a maximal run of consecutive dates in the audit period on which a team had nobody on call — with the columns team_name, gap_start, gap_end and gap_days. A team with no shift at all has one gap covering the whole period; a fully covered team has no row. Order the rows by team_name, then gap_start.

Tables

Table: OnCallTeam

ColumnType
team_idint
team_namevarchar

Primary key: team_id.

One row per team with an on-call duty. Names are unique.

Table: OnCallShift

ColumnType
shift_idint
team_idint
engineervarchar
start_datedate
end_datedate

Primary key: shift_id.

One row per scheduled shift; end_date is never before start_date.

Example

The rows must come back in the order shown.

OnCallTeam

team_idteam_name
1Payments
2Search
3Platform

OnCallShift

shift_idteam_idengineerstart_dateend_date
11Rahul2025-02-242025-03-03
21Neha2025-03-062025-03-09
31Kabir2025-03-082025-03-12
42Ishaan2025-03-012025-03-07
52Saanvi2025-03-082025-03-20

Output

team_namegap_startgap_endgap_days
Payments2025-03-042025-03-052
Payments2025-03-132025-03-142
Platform2025-03-012025-03-1414
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.

Gaps in the On-Call Rota — SQL Dates Problem

Each team must have someone on call every day. A shift covers every date from start_date to end_date, both inclusive; shifts may overlap and may start…

  • Difficulty: Hard
  • Topics: Dates, Window Functions, Subqueries
  • Dialect: MySQL
  • Problem: #344

Problem statement

Each team must have someone on call every day. A shift covers every date from start_date to end_date, both inclusive; shifts may overlap and may start before or end after the period. The audit covers 2025-03-01 to 2025-03-14.

Report every gap — a maximal run of consecutive dates in the audit period on which a team had nobody on call — with the columns team_name, gap_start, gap_end and gap_days. A team with no shift at all has one gap covering the whole period; a fully covered team has no row. Order the rows by team_name, then gap_start.

Tables

Table: OnCallTeam

ColumnType
team_idint
team_namevarchar

Primary key: team_id.

One row per team with an on-call duty. Names are unique.

Table: OnCallShift

ColumnType
shift_idint
team_idint
engineervarchar
start_datedate
end_datedate

Primary key: shift_id.

One row per scheduled shift; end_date is never before start_date.

Examples

Example 1

OnCallTeam

team_idteam_name
1Payments
2Search
3Platform

OnCallShift

shift_idteam_idengineerstart_dateend_date
11Rahul2025-02-242025-03-03
21Neha2025-03-062025-03-09
31Kabir2025-03-082025-03-12
42Ishaan2025-03-012025-03-07
52Saanvi2025-03-082025-03-20

Output

team_namegap_startgap_endgap_days
Payments2025-03-042025-03-052
Payments2025-03-132025-03-142
Platform2025-03-012025-03-1414

How to solve Gaps in the On-Call Rota

A gap is a run of days on which no shift exists, and absence cannot be selected from the shift table. So the query first creates the calendar: a recursive CTE starts at 2025-03-01 and adds one day until 2025-03-14 — a bounded series of 14 rows. Cross joining it with the teams gives every (team, date) that must be covered, and NOT EXISTS keeps the pairs for which no shift of the team has start_date <= d <= end_date. Overlapping shifts and shifts reaching outside the period need no special handling: the test is per day.

The uncovered days of a team then have to be grouped into runs — gaps and islands again. Number them per team in date order; along a run of consecutive dates the date and its row number both rise by one, so DATE_SUB(d, INTERVAL rn DAY) is the same for the whole run and changes after any covered day. Grouping by it gives each gap's first day, last day and length. A team without shifts produces all 14 days and one gap; a covered team produces no uncovered days and no row.

The alternative finds the uncovered days with a LEFT JOIN anti join and labels the runs with LAG: a day starts a new gap unless it is exactly one day after the previous uncovered day, and a running sum of those starts numbers the gaps. Both cost teams × 14 probes into the shifts plus one sort.

Reference solution (MySQL)

WITH RECURSIVE days AS (
  SELECT CAST('2025-03-01' AS DATE) AS d
  UNION ALL
  SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2025-03-14'
), uncovered AS (
  SELECT t.team_id, t.team_name, days.d
  FROM OnCallTeam t CROSS JOIN days
  WHERE NOT EXISTS (
    SELECT 1 FROM OnCallShift s
    WHERE s.team_id = t.team_id AND s.start_date <= days.d AND s.end_date >= days.d
  )
), numbered AS (
  SELECT team_id, team_name, d, ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY d) AS rn
  FROM uncovered
)
SELECT team_name, MIN(d) AS gap_start, MAX(d) AS gap_end, COUNT(*) AS gap_days
FROM numbered
GROUP BY team_id, team_name, DATE_SUB(d, INTERVAL rn DAY)
ORDER BY team_name, gap_start

Another way

WITH RECURSIVE days AS (
  SELECT CAST('2025-03-01' AS DATE) AS d
  UNION ALL
  SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2025-03-14'
), uncovered AS (
  SELECT t.team_id, t.team_name, days.d
  FROM OnCallTeam t CROSS JOIN days
  LEFT JOIN OnCallShift s ON s.team_id = t.team_id AND days.d BETWEEN s.start_date AND s.end_date
  WHERE s.shift_id IS NULL
), flagged AS (
  SELECT team_id, team_name, d,
         CASE WHEN DATEDIFF(d, LAG(d) OVER (PARTITION BY team_id ORDER BY d)) = 1 THEN 0 ELSE 1 END AS new_gap
  FROM uncovered
), labelled AS (
  SELECT team_id, team_name, d,
         SUM(new_gap) OVER (PARTITION BY team_id ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS gap_no
  FROM flagged
)
SELECT team_name, MIN(d) AS gap_start, MAX(d) AS gap_end, COUNT(*) AS gap_days
FROM labelled
GROUP BY team_id, team_name, gap_no
ORDER BY team_name, gap_start

← Total Outage Minutes per Service With Overlaps Merged · Repeat Contact Rate by First-Ticket Month →