Interview Question Bank · Self-join · interval overlap · open-ended ranges
10. Overlapping prescriptions
HardHealthcareQuestion #142
Concept: Self-join · interval overlap · open-ended rangesshow
Two ranges overlap when each one starts on or before the other ends: a.start <= b.end AND b.start <= a.end. Self-join on the shared key with a.id < b.id so each pair appears once and nothing pairs with itself. An open-ended range has NULL as its end, so swap in COALESCE(end, 'infinity'); GREATEST of the starts and LEAST of the ends give the overlap itself (LEAST skips NULLs).
Example (different data):
SELECT a.room, a.id, b.id, GREATEST(a.starts_on, b.starts_on) AS clash_from FROM bookings a JOIN bookings b ON b.room = a.room AND a.id < b.id AND a.starts_on <= COALESCE(b.ends_on, 'infinity') AND b.starts_on <= COALESCE(a.ends_on, 'infinity');
Your task
Larkspur Health's pharmacists review patients taking two medications at the same time. end_date is the last day of supply (inclusive), and NULL means the prescription is ongoing.
For every pair of prescriptions for the same patient whose date ranges overlap by at least one day, return patient (name), drug_a (the prescription with the lower id), drug_b, overlap_start and overlap_end (NULL if both are ongoing). Sort by patient, overlap_start, drug_a, then drug_b.
Starting Postgres in your browser…
Checking your access…