Healthcare: Patient Wait
United Health, CVS Health
Problem Statement
Patient satisfaction is closely linked to wait times. UnitedHealth needs to monitor the operational efficiency of different clinics by analyzing how long a patient waits after checking in before a doctor is assigned to them.
Write a query to calculate the median wait time in minutes for each clinic.
Rules:
- A single patient visit is identified by a unique patient_id and visit_date.
- A wait time is the difference between the timestamp of the Check-in event and the Doctor Assigned event for the same visit.
- If a patient checks in but is never assigned a doctor, that visit should be ignored.
- The output median wait time should be rounded to 1 decimal place.
- Output columns: clinic_id, median_wait_minutes.
- Result must be sorted by median_wait_minutes descending.
Tests your understanding of
Window Functions, Median and Time Difference
Input Tables
patient_logs
| log_id(INTEGER) | clinic_id(INTEGER) | patient_id(INTEGER) | event_type(VARCHAR) | event_timestamp(TIMESTAMP) |
|---|---|---|---|---|
| 1 | 1 | 501 | Check-in | 2026-03-04 09:00:00 |
| 2 | 1 | 501 | Doctor Assigned | 2026-03-04 09:15:00 |
| 3 | 1 | 502 | Check-in | 2026-03-04 09:10:00 |
| 4 | 1 | 502 | Doctor Assigned | 2026-03-04 09:40:00 |
| 5 | 1 | 503 | Check-in | 2026-03-04 10:00:00 |
| 6 | 1 | 503 | Doctor Assigned | 2026-03-04 10:10:00 |
| 7 | 2 | 601 | Check-in | 2026-03-04 08:00:00 |
| 8 | 2 | 601 | Doctor Assigned | 2026-03-04 08:45:00 |
| 9 | 2 | 602 | Check-in | 2026-03-04 08:30:00 |
| 10 | 2 | 602 | Doctor Assigned | 2026-03-04 09:05:00 |
Expected Output
| clinic_id(INTEGER) | median_wait_minutes(DECIMAL) |
|---|---|
| 2 | 40 |
| 1 | 15 |
Tags
HardWindow FunctionsMedianTime Difference
30-40 min
34%