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
| Column | Type |
|---|---|
| team_id | int |
| team_name | varchar |
Primary key: team_id.
One row per team with an on-call duty. Names are unique.
Table: OnCallShift
| Column | Type |
|---|---|
| shift_id | int |
| team_id | int |
| engineer | varchar |
| start_date | date |
| end_date | date |
Primary key: shift_id.
One row per scheduled shift; end_date is never before start_date.
Examples
Example 1
OnCallTeam
| team_id | team_name |
|---|---|
| 1 | Payments |
| 2 | Search |
| 3 | Platform |
OnCallShift
| shift_id | team_id | engineer | start_date | end_date |
|---|---|---|---|---|
| 1 | 1 | Rahul | 2025-02-24 | 2025-03-03 |
| 2 | 1 | Neha | 2025-03-06 | 2025-03-09 |
| 3 | 1 | Kabir | 2025-03-08 | 2025-03-12 |
| 4 | 2 | Ishaan | 2025-03-01 | 2025-03-07 |
| 5 | 2 | Saanvi | 2025-03-08 | 2025-03-20 |
Output
| team_name | gap_start | gap_end | gap_days |
|---|---|---|---|
| Payments | 2025-03-04 | 2025-03-05 | 2 |
| Payments | 2025-03-13 | 2025-03-14 | 2 |
| Platform | 2025-03-01 | 2025-03-14 | 14 |
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_startAnother 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 →