OPD No-Show Rate by Doctor — SQL Aggregation Problem
Patients who book an OPD slot and never turn up waste a doctor's time. Only appointments that were due count: status completed or no_show.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #206
Problem statement
Patients who book an OPD slot and never turn up waste a doctor's time. Only appointments that were due count: status completed or no_show. Cancelled and still-scheduled bookings are ignored.
For every doctor with at least 3 due appointments, return doctor_id, name, due_appointments, no_shows and no_show_pct = 100 × no_shows ÷ due_appointments, rounded to 2 decimals. Order by no_show_pct descending, then doctor_id.
Tables
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| name | varchar |
| department | varchar |
Primary key: doctor_id.
Table: Appointment
| Column | Type |
|---|---|
| appt_id | int |
| doctor_id | int |
| patient_id | int |
| slot_at | datetime |
| status | enum(scheduled, completed, no_show, cancelled) |
Primary key: appt_id.
One row per OPD booking; doctor_id always exists in Doctor.
Examples
Example 1
Doctor
| doctor_id | name | department |
|---|---|---|
| 1 | Kavya Menon | Cardiology |
| 2 | Rohan Gupta | Orthopaedics |
| 3 | Aisha Khan | Dermatology |
| 4 | Vikram Joshi | ENT |
Appointment
| appt_id | doctor_id | patient_id | slot_at | status |
|---|---|---|---|---|
| 101 | 1 | 501 | 2025-05-05 10:00:00 | completed |
| 102 | 1 | 502 | 2025-05-05 10:15:00 | no_show |
| 103 | 1 | 503 | 2025-05-05 10:30:00 | completed |
| 104 | 1 | 504 | 2025-05-05 10:45:00 | cancelled |
| 105 | 2 | 505 | 2025-05-05 11:00:00 | no_show |
| 106 | 2 | 506 | 2025-05-06 11:00:00 | no_show |
| 107 | 2 | 507 | 2025-05-06 11:15:00 | completed |
| 108 | 2 | 508 | 2025-05-07 11:15:00 | completed |
| 109 | 3 | 509 | 2025-05-06 09:00:00 | completed |
| 110 | 3 | 510 | 2025-05-06 09:20:00 | completed |
| 111 | 3 | 511 | 2025-05-08 09:00:00 | completed |
| 112 | 4 | 512 | 2025-05-08 12:00:00 | no_show |
| 113 | 4 | 513 | 2025-05-08 12:20:00 | completed |
| 114 | 4 | 514 | 2025-05-20 12:00:00 | scheduled |
Output
| doctor_id | name | due_appointments | no_shows | no_show_pct |
|---|---|---|---|---|
| 2 | Rohan Gupta | 4 | 2 | 50 |
| 1 | Kavya Menon | 3 | 1 | 33.33 |
| 3 | Aisha Khan | 3 | 0 | 0 |
How to solve OPD No-Show Rate by Doctor
The rate's denominator is only the appointments that were due, so the cleanest plan is to remove cancelled and scheduled bookings in WHERE before grouping. Every remaining row is either completed or a no-show, and per doctor COUNT(*) is the number due.
The no-shows are counted with conditional aggregation: SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) adds one for every no-show in the group. The percentage is that sum times 100 divided by the count, rounded to 2 decimals — MySQL keeps four decimal places in a division, so the rounding makes the value well defined. Multiplying by 100 before dividing keeps the arithmetic exact.
Doctors with fewer than three due appointments would produce noisy 0 % or 100 % rates, so HAVING COUNT(*) >= 3 drops them after grouping. Ties in the rate are broken by doctor_id. A CTE that aggregates Appointment alone and joins the doctors afterwards is the same plan with the join done on fewer rows.
Reference solution (MySQL)
SELECT d.doctor_id, d.name,
COUNT(*) AS due_appointments,
SUM(CASE WHEN a.status = 'no_show' THEN 1 ELSE 0 END) AS no_shows,
ROUND(100 * SUM(CASE WHEN a.status = 'no_show' THEN 1 ELSE 0 END) / COUNT(*), 2) AS no_show_pct
FROM Doctor d
JOIN Appointment a ON a.doctor_id = d.doctor_id
WHERE a.status IN ('completed', 'no_show')
GROUP BY d.doctor_id, d.name
HAVING COUNT(*) >= 3
ORDER BY no_show_pct DESC, d.doctor_idAnother way
WITH due AS (
SELECT doctor_id, COUNT(*) AS due_appointments, SUM(IF(status = 'no_show', 1, 0)) AS no_shows
FROM Appointment WHERE status <> 'cancelled' AND status <> 'scheduled'
GROUP BY doctor_id
)
SELECT d.doctor_id, d.name, u.due_appointments, u.no_shows, ROUND(u.no_shows * 100 / u.due_appointments, 2) AS no_show_pct
FROM due u JOIN Doctor d ON d.doctor_id = u.doctor_id
WHERE u.due_appointments >= 3
ORDER BY no_show_pct DESC, d.doctor_id← Average Consultation Fee by Specialty · Patients Readmitted Within 30 Days of Discharge →