Patients With Three or More No-Shows in a Row

Hard
218.

Patients With Three or More No-Shows in a Row

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

ColumnTypeValues
appt_idint
patient_idint
slot_atdatetime
statusenumcompleted, no_show, cancelled

Primary key: appt_id.

A patient never has two appointments at the same slot_at.

Example

The rows must come back in the order shown.

Appointment

appt_idpatient_idslot_atstatus
1112025-01-06 10:00:00no_show
2112025-01-13 10:00:00cancelled
3112025-01-20 10:00:00no_show
4112025-01-27 10:00:00no_show
5112025-02-03 10:00:00completed
6122025-01-07 11:30:00no_show
7122025-01-14 11:30:00no_show
8122025-01-21 11:30:00completed
9122025-01-28 11:30:00no_show
10132025-01-08 09:00:00no_show
3 more rows

Output

patient_idlongest_no_show_run
134
113
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
appt_idint
patient_idint
slot_atdatetime
statusenum(completed, no_show, cancelled)

Primary key: appt_id.

A patient never has two appointments at the same slot_at.

Examples

Example 1

Appointment

appt_idpatient_idslot_atstatus
1112025-01-06 10:00:00no_show
2112025-01-13 10:00:00cancelled
3112025-01-20 10:00:00no_show
4112025-01-27 10:00:00no_show
5112025-02-03 10:00:00completed
6122025-01-07 11:30:00no_show
7122025-01-14 11:30:00no_show
8122025-01-21 11:30:00completed
9122025-01-28 11:30:00no_show
10132025-01-08 09:00:00no_show
11132025-01-09 09:00:00no_show
12132025-01-10 09:00:00no_show
13132025-01-11 09:00:00no_show

Output

patient_idlongest_no_show_run
134
113

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_id

Another 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 →