Drill 8 of 12 in Healthcare questions
0

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…