OPD No-Show Rate by Doctor

Medium
206.

OPD No-Show Rate by Doctor

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

ColumnType
doctor_idint
namevarchar
departmentvarchar

Primary key: doctor_id.

Table: Appointment

ColumnTypeValues
appt_idint
doctor_idint
patient_idint
slot_atdatetime
statusenumscheduled, completed, no_show, cancelled

Primary key: appt_id.

One row per OPD booking; doctor_id always exists in Doctor.

Example

The rows must come back in the order shown.

Doctor

doctor_idnamedepartment
1Kavya MenonCardiology
2Rohan GuptaOrthopaedics
3Aisha KhanDermatology
4Vikram JoshiENT

Appointment

appt_iddoctor_idpatient_idslot_atstatus
10115012025-05-05 10:00:00completed
10215022025-05-05 10:15:00no_show
10315032025-05-05 10:30:00completed
10415042025-05-05 10:45:00cancelled
10525052025-05-05 11:00:00no_show
10625062025-05-06 11:00:00no_show
10725072025-05-06 11:15:00completed
10825082025-05-07 11:15:00completed
10935092025-05-06 09:00:00completed
11035102025-05-06 09:20:00completed
4 more rows

Output

doctor_idnamedue_appointmentsno_showsno_show_pct
2Rohan Gupta4250
1Kavya Menon3133.33
3Aisha Khan300
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.

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

ColumnType
doctor_idint
namevarchar
departmentvarchar

Primary key: doctor_id.

Table: Appointment

ColumnType
appt_idint
doctor_idint
patient_idint
slot_atdatetime
statusenum(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_idnamedepartment
1Kavya MenonCardiology
2Rohan GuptaOrthopaedics
3Aisha KhanDermatology
4Vikram JoshiENT

Appointment

appt_iddoctor_idpatient_idslot_atstatus
10115012025-05-05 10:00:00completed
10215022025-05-05 10:15:00no_show
10315032025-05-05 10:30:00completed
10415042025-05-05 10:45:00cancelled
10525052025-05-05 11:00:00no_show
10625062025-05-06 11:00:00no_show
10725072025-05-06 11:15:00completed
10825082025-05-07 11:15:00completed
10935092025-05-06 09:00:00completed
11035102025-05-06 09:20:00completed
11135112025-05-08 09:00:00completed
11245122025-05-08 12:00:00no_show
11345132025-05-08 12:20:00completed
11445142025-05-20 12:00:00scheduled

Output

doctor_idnamedue_appointmentsno_showsno_show_pct
2Rohan Gupta4250
1Kavya Menon3133.33
3Aisha Khan300

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_id

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