Overlapping Appointments

MediumSQL · PostgreSQL

Problem

Providence uses appointment data from its electronic health record to identify patients who were scheduled for overlapping encounters. Write a PostgreSQL query that reports each overlapping appointment pair for March 10, 2025.

Requirements

Consider only appointments with status scheduled, checked_in, or completed.
Treat two appointments as overlapping when one starts before the other ends and the other starts before the first ends. Appointments that only touch at an endpoint do not overlap.
Return each pair once, excluding self-pairs and reversed duplicates.
Include patient details, both appointment intervals, both provider names, and the overlap duration in minutes.
Preserve an overlap even when a provider is missing, and sort by patient ID, first appointment start time, and second appointment start time.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
first_name	VARCHAR(50)	Patient first name
last_name	VARCHAR(50)	Patient last name

providers

Column	Type	Description
provider_idPK	INT	Unique provider identifier
provider_name	VARCHAR(100)	Provider display name
specialty	VARCHAR(80)	Clinical specialty

appointments

Column	Type	Description
appointment_idPK	INT	Unique appointment identifier
patient_id	INT	Patient receiving the appointment
provider_id	INT	Assigned provider
appointment_date	DATE	Calendar date of the appointment
start_time	TIMESTAMP	Appointment start timestamp
end_time	TIMESTAMP	Appointment end timestamp
status	VARCHAR(20)	Appointment lifecycle status

Tablesappointmentspatientsproviders