Turning a messy scheduling export into a patient no-show risk model
An end-to-end SQL and Power BI project: cleaning a raw outpatient appointment export, building a window-function risk model that scores each appointment from a patient's own history, and shipping the result as a dashboard clinic staff can act on.
The problem
A missed appointment is a wasted clinical slot, a patient who doesn't get seen, and a scheduling team that finds out too late to backfill the time. Clinics that can see which appointments are likely to be no-shows — before the day arrives — can send reminders, overbook strategically, or call patients directly.
This project builds that visibility from scratch: a raw six-week outpatient scheduling export, cleaned and modeled entirely in SQL, then surfaced as a Power BI dashboard with a live high-risk patient list.
Dataset & tools
Each row is one scheduled outpatient visit: patient and appointment IDs, gender and age, neighbourhood, welfare-scholarship enrollment, three chronic-condition flags (hypertension, diabetes, alcoholism), a disability count, whether an SMS reminder was sent, the raw scheduling and appointment timestamps, and the no-show outcome.
- MySQL Workbench — all cleaning and analytical SQL, including a reusable risk-scoring view
- Power BI Desktop — Power Query transforms, a DAX measure layer, and the dashboard build
clinical_appt
against 110,527 expected — a partial import, not a cleanup artifact. Diagnosed by comparing row
counts and date ranges against v_appointment_risk, then fixed by re-syncing the table.
Every number on this page is computed after that fix, on the full, correctly-joined dataset.
Cleaning the raw export
The export arrived with cryptic column names, timestamps stored as ISO-8601 strings, and a few impossible values that would have quietly corrupted every downstream metric.
- Renamed unclear columns:
Hipertension→Hypertension,Handcap→disability_count,`No-show`→no_show - Parsed
ScheduledDay/AppointmentDayout of"2016-04-29T18:38:08Z"-style strings into realDATETIME/DATEcolumns - Dropped one record with
age = -1, a data-entry error - Derived
lead_time_daysand removed appointments with a negative lead time — logged before they were scheduled
-- parse the ISO timestamp strings into real datetime columns UPDATE clinical_appt SET ScheduledDay_clean = STR_TO_DATE(REPLACE(REPLACE(ScheduledDay, 'T', ' '), 'Z', ''), '%Y-%m-%d %H:%i:%S'), AppointmentDay_clean = STR_TO_DATE(REPLACE(REPLACE(AppointmentDay, 'T', ' '), 'Z', ''), '%Y-%m-%d %H:%i:%S'); -- derive booking lead time, then drop the impossible negatives ALTER TABLE clinical_appt ADD COLUMN lead_time_days INT; UPDATE clinical_appt SET lead_time_days = DATEDIFF(AppointmentDay, DATE(ScheduledDay)); DELETE FROM clinical_appt WHERE lead_time_days < 0;
Scoring risk from patient history
The core of the project is a per-patient risk score built entirely with window functions. For every
appointment, a running count of that patient's prior appointments and prior no-shows is
computed — using ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING so the score
only ever looks backward from the appointment being scored. That guards against leaking the outcome
of the very appointment the model is trying to predict.
Those running totals feed a CASE-based classifier, published as a view so both SQL
consumers and Power BI can query it directly:
-- prior activity, computed only from appointments before this one COUNT(*) OVER ( PARTITION BY PatientId ORDER BY AppointmentDay ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS prior_appointments, -- classify each appointment from that history CASE WHEN prior_appointments = 0 THEN 'New Patient - Monitor' WHEN prior_no_show_rate >= 0.5 OR lead_time_days > 8 THEN 'High Risk' WHEN prior_no_show_rate >= 0.2 OR lead_time_days BETWEEN 4 AND 7 THEN 'Medium Risk' ELSE 'Low Risk' END AS risk_tier
What the data showed
Lead time is the strongest single driver
Same-day bookings almost never no-show. Book 30+ days out and the rate is roughly 7× higher.
The risk tiers separate cleanly
Across all 110,521 scored appointments, Low-Risk no-shows at 9.3% against 31.4% for High-Risk — a 3.4× spread.
Day of week is a mild, not major, factor
Friday runs highest, Thursday lowest — a real but modest ~2-point spread across the work week.
SMS reminders correlate with more no-shows
Not causal — reminders are sent disproportionately to longer-lead-time appointments, which already run riskier.
No-show risk peaks in the teens, falls with age
13–18 year-olds no-show most; patients 65+ are the most reliable age group.
Risk concentrates geographically
Santos Dumont tops 81 neighbourhoods for no-show rate (min. 300 appts) — the ten highest run 22–29%, well above the citywide average.
The dashboard
The risk view and cleaned appointment table feed a Power BI report built for front-desk and scheduling staff: total appointments, no-show rate, and average lead time up top; no-show rate by day of week and by lead-time band; the risk-tier breakdown; a neighbourhood ranking; behavioral and demographic drivers; and a searchable, filterable list of high-risk patients for outreach.
Skills demonstrated
Resume-ready summary
Copy-paste bullets
- Cleaned and modeled 110,521 outpatient appointment records in MySQL, resolving malformed timestamps, mistyped columns, and invalid values that would have skewed every downstream metric.
- Engineered a patient-level no-show risk score using SQL window functions, computing prior-visit history without leaking same-day outcomes, and published it as a reusable view.
- Built a rule-based four-tier risk classifier that separated High- and Low-Risk patients by a 3.4× gap in no-show rate (31.4% vs 9.3%) across the full dataset.
- Designed a Power BI dashboard with DAX-driven KPIs, trend breakdowns, and a searchable high-risk patient filter covering 13,957 flagged patients for clinic outreach.