Interview Question Bank · EXISTS · self-reference · inclusive date window · observation cutoff
9. 30-day readmission rate
HardHealthcareQuestion #141
Concept: EXISTS · self-reference · inclusive date window · observation cutoffshow
A readmission rate asks, for each finished stay, whether the same patient came back within N days. EXISTS answers yes or no once per stay; a plain self-join returns one row per return visit and double counts. Exclude the stay itself, count the boundary day, and only use stays that ended early enough for the full window to be observed.
Example (different data):
WITH o AS (
SELECT o.region,
EXISTS (SELECT 1 FROM orders r WHERE r.customer_id = o.customer_id AND r.id <> o.id
AND r.ordered_on BETWEEN o.ordered_on AND o.ordered_on + 14) AS reordered
FROM orders o WHERE o.ordered_on <= DATE '2025-03-17'
)
SELECT region, ROUND(100.0 * COUNT(*) FILTER (WHERE reordered) / COUNT(*), 1) AS pct
FROM o GROUP BY region;Your task
Larkspur Health is measured on 30-day readmissions. The data is a snapshot from 2025-06-30, so only stays discharged on or before 2025-05-31 have a full 30 days to observe.
Index stays are stays discharged on or before 2025-05-31. An index stay is readmitted if the same patient has another admission whose admitted_on is 0 to 30 days after its discharged_on (both ends included), in any department. Return department (of the index stay), index_stays, readmissions (index stays that were readmitted) and readmission_rate_pct (rounded to 1 decimal). Sort by readmission_rate_pct descending, then department.
Starting Postgres in your browser…
Checking your access…