Drill 10 of 12 in Healthcare questions
0

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…