Double-Booked Doctor Appointment Slots

Hard
221.

Double-Booked Doctor Appointment Slots

HardJoinsDates

MySQL

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

ColumnTypeValues
appt_idint
doctor_idint
patient_idint
starts_atdatetime
ends_atdatetime
statusenumbooked, completed, cancelled

Primary key: appt_id.

ends_at is always after starts_at; times are on whole minutes.

Example

The rows must come back in the order shown.

Appointment

appt_iddoctor_idpatient_idstarts_atends_atstatus
175012025-05-12 10:00:002025-05-12 10:30:00completed
275022025-05-12 10:20:002025-05-12 10:40:00completed
375032025-05-12 10:40:002025-05-12 11:00:00booked
475042025-05-12 10:05:002025-05-12 10:15:00booked
585052025-05-12 10:00:002025-05-12 11:00:00booked
685062025-05-12 10:30:002025-05-12 10:45:00cancelled
795072025-05-12 09:00:002025-05-12 09:30:00booked

Output

doctor_idfirst_apptsecond_apptoverlap_minutes
71210
71410
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.

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

ColumnType
appt_idint
doctor_idint
patient_idint
starts_atdatetime
ends_atdatetime
statusenum(booked, completed, cancelled)

Primary key: appt_id.

ends_at is always after starts_at; times are on whole minutes.

Examples

Example 1

Appointment

appt_iddoctor_idpatient_idstarts_atends_atstatus
175012025-05-12 10:00:002025-05-12 10:30:00completed
275022025-05-12 10:20:002025-05-12 10:40:00completed
375032025-05-12 10:40:002025-05-12 11:00:00booked
475042025-05-12 10:05:002025-05-12 10:15:00booked
585052025-05-12 10:00:002025-05-12 11:00:00booked
685062025-05-12 10:30:002025-05-12 10:45:00cancelled
795072025-05-12 09:00:002025-05-12 09:30:00booked

Output

doctor_idfirst_apptsecond_apptoverlap_minutes
71210
71410

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_appt

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