Double-Booked Doctor Appointment Slots — SQL Joins Problem
Reception sometimes books a doctor into two appointments that overlap in time.
- Difficulty: Hard
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #221
Problem statement
Reception sometimes books a doctor into two appointments that overlap in time. Two appointments of the same doctor overlap when each starts before the other ends; one ending exactly when the next starts is not an overlap. Cancelled appointments block nothing.
Return every overlapping pair once, with doctor_id, first_appt (the smaller appt_id), second_appt and overlap_minutes (how long both run at the same time). Order by doctor_id, first_appt, second_appt.
Tables
Table: Appointment
| Column | Type |
|---|---|
| appt_id | int |
| doctor_id | int |
| patient_id | int |
| starts_at | datetime |
| ends_at | datetime |
| status | enum(booked, completed, cancelled) |
Primary key: appt_id.
ends_at is always after starts_at; times are on whole minutes.
Examples
Example 1
Appointment
| appt_id | doctor_id | patient_id | starts_at | ends_at | status |
|---|---|---|---|---|---|
| 1 | 7 | 501 | 2025-05-12 10:00:00 | 2025-05-12 10:30:00 | completed |
| 2 | 7 | 502 | 2025-05-12 10:20:00 | 2025-05-12 10:40:00 | completed |
| 3 | 7 | 503 | 2025-05-12 10:40:00 | 2025-05-12 11:00:00 | booked |
| 4 | 7 | 504 | 2025-05-12 10:05:00 | 2025-05-12 10:15:00 | booked |
| 5 | 8 | 505 | 2025-05-12 10:00:00 | 2025-05-12 11:00:00 | booked |
| 6 | 8 | 506 | 2025-05-12 10:30:00 | 2025-05-12 10:45:00 | cancelled |
| 7 | 9 | 507 | 2025-05-12 09:00:00 | 2025-05-12 09:30:00 | booked |
Output
| doctor_id | first_appt | second_appt | overlap_minutes |
|---|---|---|---|
| 7 | 1 | 2 | 10 |
| 7 | 1 | 4 | 10 |
How to solve Double-Booked Doctor Appointment Slots
Finding conflicts between rows of the same table is a self join. Pair every appointment a with every other appointment b of the same doctor, and require a.appt_id < b.appt_id so each unordered pair appears once and no appointment is paired with itself.
The overlap test for intervals is the standard one: a.starts_at < b.ends_at AND b.starts_at < a.ends_at. It covers every arrangement — partial overlap on either side and one interval containing the other (appointments 1 and 4 in the example) — and the strict inequalities make back-to-back slots, where one ends at 10:40 and the next starts at 10:40, not count. Its negation, "one ends before the other starts", is the alternative's NOT (…) form.
The shared stretch starts at the later of the two starts and ends at the earlier of the two ends, so TIMESTAMPDIFF(MINUTE, GREATEST(starts), LEAST(ends)) is the overlap. Cancelled appointments are removed on both sides. The join is quadratic per doctor-day; an index on (doctor_id, starts_at) keeps it small in practice.
Reference solution (MySQL)
SELECT a.doctor_id, a.appt_id AS first_appt, b.appt_id AS second_appt,
TIMESTAMPDIFF(MINUTE, GREATEST(a.starts_at, b.starts_at), LEAST(a.ends_at, b.ends_at)) AS overlap_minutes
FROM Appointment a
JOIN Appointment b
ON b.doctor_id = a.doctor_id
AND a.appt_id < b.appt_id
AND a.starts_at < b.ends_at
AND b.starts_at < a.ends_at
WHERE a.status <> 'cancelled' AND b.status <> 'cancelled'
ORDER BY a.doctor_id, first_appt, second_apptAnother way
SELECT a.doctor_id, a.appt_id AS first_appt, b.appt_id AS second_appt,
TIMESTAMPDIFF(MINUTE, CASE WHEN a.starts_at > b.starts_at THEN a.starts_at ELSE b.starts_at END, CASE WHEN a.ends_at < b.ends_at THEN a.ends_at ELSE b.ends_at END) AS overlap_minutes
FROM (SELECT * FROM Appointment WHERE status IN ('booked', 'completed')) a
JOIN (SELECT * FROM Appointment WHERE status IN ('booked', 'completed')) b ON a.doctor_id = b.doctor_id AND b.appt_id > a.appt_id
WHERE NOT (a.ends_at <= b.starts_at OR b.ends_at <= a.starts_at)
ORDER BY 1, 2, 3← Insurance Claim Funnel by Insurer · Daily Admissions With Quiet Days and a Running Total →