Patients With Three or More No-Shows in a Row — SQL Problem
The OPD wants to ask patients who repeatedly miss appointments to pay a booking deposit. Look at each patient's appointments in slot_at order.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic
- Dialect: MySQL
- Problem: #218
Problem statement
The OPD wants to ask patients who repeatedly miss appointments to pay a booking deposit. Look at each patient's appointments in slot_at order. Cancelled appointments are ignored completely — they neither count as a no-show nor break a run. A completed appointment breaks a run.
Return patient_id and longest_no_show_run (the most consecutive no-shows) for every patient whose longest run is at least 3. Order by longest_no_show_run descending, then patient_id.
Tables
Table: Appointment
| Column | Type |
|---|---|
| appt_id | int |
| patient_id | int |
| slot_at | datetime |
| status | enum(completed, no_show, cancelled) |
Primary key: appt_id.
A patient never has two appointments at the same slot_at.
Examples
Example 1
Appointment
| appt_id | patient_id | slot_at | status |
|---|---|---|---|
| 1 | 11 | 2025-01-06 10:00:00 | no_show |
| 2 | 11 | 2025-01-13 10:00:00 | cancelled |
| 3 | 11 | 2025-01-20 10:00:00 | no_show |
| 4 | 11 | 2025-01-27 10:00:00 | no_show |
| 5 | 11 | 2025-02-03 10:00:00 | completed |
| 6 | 12 | 2025-01-07 11:30:00 | no_show |
| 7 | 12 | 2025-01-14 11:30:00 | no_show |
| 8 | 12 | 2025-01-21 11:30:00 | completed |
| 9 | 12 | 2025-01-28 11:30:00 | no_show |
| 10 | 13 | 2025-01-08 09:00:00 | no_show |
| 11 | 13 | 2025-01-09 09:00:00 | no_show |
| 12 | 13 | 2025-01-10 09:00:00 | no_show |
| 13 | 13 | 2025-01-11 09:00:00 | no_show |
Output
| patient_id | longest_no_show_run |
|---|---|
| 13 | 4 |
| 11 | 3 |
How to solve Patients With Three or More No-Shows in a Row
Removing the cancelled rows up front makes the rule in the statement automatic: what remains is a sequence of completed and no-show appointments per patient, and a run is a maximal block of consecutive no-shows in it.
The double ROW_NUMBER trick labels the blocks. Number each patient's rows by slot_at (rn_all) and, separately, each patient's rows of the same status (rn_status). While the status stays the same, both increase by one per row, so rn_all − rn_status is constant; when a completed visit interrupts, rn_all advances without rn_status for no-shows, so the next no-show block gets a new label. Grouping the no-show rows by patient and label gives each run's length; the longest per patient, kept when it is at least 3, is the answer.
The alternative counts the completed visits seen so far with a running SUM: every no-show between two completed visits shares the same count, so that count is the block id. Both approaches sort each patient's appointments once; ties in the final order fall back to patient_id.
Reference solution (MySQL)
WITH kept AS (
SELECT patient_id, status,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY slot_at)
- ROW_NUMBER() OVER (PARTITION BY patient_id, status ORDER BY slot_at) AS grp
FROM Appointment
WHERE status <> 'cancelled'
), runs AS (
SELECT patient_id, grp, COUNT(*) AS run_len
FROM kept
WHERE status = 'no_show'
GROUP BY patient_id, grp
)
SELECT patient_id, MAX(run_len) AS longest_no_show_run
FROM runs
GROUP BY patient_id
HAVING MAX(run_len) >= 3
ORDER BY longest_no_show_run DESC, patient_idAnother way
WITH marked AS (
SELECT patient_id, status,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) OVER (PARTITION BY patient_id ORDER BY slot_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS block
FROM Appointment WHERE status IN ('completed', 'no_show')
), runs AS (
SELECT patient_id, block, SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) AS run_len FROM marked GROUP BY patient_id, block
)
SELECT patient_id, MAX(run_len) AS longest_no_show_run FROM runs GROUP BY patient_id
HAVING MAX(run_len) > 2 ORDER BY 2 DESC, 1← Median Length of Stay by Ward · OPD New-Patient Retention by Monthly Cohort →