Interview Question Bank · Correlated MIN · PERCENTILE_CONT · ignoring NULLs
8. Median wait from referral to first visit
MediumHealthcareQuestion #140
Concept: Correlated MIN · PERCENTILE_CONT · ignoring NULLsshow
Wait time is measured per referral: find the first qualifying event on or after the start date with MIN, subtract, then take the median. A scalar subquery per referral returns NULL when nothing qualifies, which keeps the referral in the count while PERCENTILE_CONT skips it. Median beats average here because a few long waits pull the mean up.
Example (different data):
WITH w AS (
SELECT t.category,
(SELECT MIN(r.replied_at::date) FROM replies r
WHERE r.ticket_id = t.id AND r.replied_at >= t.opened_at) - t.opened_at::date AS days
FROM tickets t
)
SELECT category, COUNT(*) AS tickets, COUNT(days) AS answered,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days) AS median_days
FROM w GROUP BY category;Your task
Larkspur Health's access team tracks how long referred patients wait to be seen. A referral is seen at the patient's first completed appointment with a doctor of the referred specialty, on or after the referral date.
For each specialty, return specialty, referrals (all referrals), seen (referrals with such an appointment) and median_days (PERCENTILE_CONT median of days from referred_on to that first appointment, over seen referrals, rounded to 1 decimal). Sort by specialty.
Starting Postgres in your browser…
Checking your access…