Detect Double-Booked Clinician Slots | Dataford Interview Questions

Given a database of clinician schedules, write a query to find overlapping or double-booked time slots at Talkiatry. Include only active booked slots for the same clinician and calendar date. Treat slots that touch at an endpoint as non-overlapping.

Output

One row per overlapping slot pair, with clinician identity, both schedule IDs, and both start and end timestamps.
Exclude cancelled or incomplete slots.
Order by clinician ID, first slot start time, second slot start time, and schedule IDs.

Schema

clinicians

Column	Type	Description
clinician_idPK	INT	Unique clinician identifier
full_name	VARCHAR(150)	Clinician's display name
specialty	VARCHAR(100)	Clinical specialty

clinician_schedules

Column	Type	Description
schedule_idPK	INT	Unique schedule slot identifier
clinician_id	INT	Clinician assigned to the slot
schedule_date	DATE	Calendar date of the slot
start_at	TIMESTAMP	Slot start timestamp
end_at	TIMESTAMP	Slot end timestamp
status	VARCHAR(30)	Booking status

Your question is Detect Double-Booked Clinician Slots. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem