medium Same-Day Appointment Fulfillment

The whole problem is a fraction whose numerator and denominator are computed over the same set of rows — each patient's first appointment — so the only real work is pinning down that set once and counting it two ways: how many exist, and how many were booked for the same day they were requested. A patient's first appointment is the row with the minimum booking_date; an appointment is immediate when booking_date = patient_pref_date. The answer is 100 × immediate_first / total_first, rounded to two places.

The mechanism that makes this clean: derive one row per patient holding their earliest booking date, then probe the original table to ask "was that first appointment immediate?" The hazard — and the part the naive solution gets subtly wrong — is the probe step, because matching on a date is not the same as matching on the identity of a single row.

Worked example

The seven input rows collapse to four patients. For each, take the row with the smallest booking_date; that is the first appointment. Then compare its booking_date to its patient_pref_date.

Patient 40 is the trap row a careless reader misses: appointment_id 13 has the later date 07-22, so the first appointment is id 14 on 07-18, whose pref date 07-19 differs — scheduled, not immediate. Totals: 4 first appointments, 2 immediate. So 100 × 2 / 4 = 50.00.

One ratio computed directly with a CTE for the first-appointment set and scalar subqueries for the two counts:

WITH FirstAppointments AS (
    SELECT patient_id, MIN(booking_date) AS first_booking_date
    FROM   Appointments
    GROUP  BY patient_id
)
SELECT ROUND(
         (SELECT COUNT(*)
          FROM   FirstAppointments fa
          JOIN   Appointments a
            ON   a.patient_id   = fa.patient_id
           AND   a.booking_date = fa.first_booking_date
          WHERE  a.booking_date = a.patient_pref_date)
         / (SELECT COUNT(*) FROM FirstAppointments)
         * 100, 2) AS immediate_percentage;

In MySQL the / operator always returns a DECIMAL, so 2 / 4 evaluates to 0.5000, not 0 — there is no integer-truncation bug here the way there would be in a language with integer division. Multiplying by 100 then ROUND(...,2) gives 50.00.

Why the join probe is the dangerous step

The natural way to ask "was this patient's first appointment immediate?" is to join the CTE back to Appointments on patient_id and booking_date = first_booking_date, then filter for immediacy. That works only when each patient has at most one row on their earliest date. Read the join key carefully: it matches on a date, not on a row identity. appointment_id being unique does not save you — uniqueness of the primary key says nothing about how many rows share a (patient_id, booking_date) pair.

Suppose patient 30 had booked two appointments on 2020-07-02, one immediate and one scheduled:

The CTE still produces one row for patient 30 (denominator counts them once). But the join on first_booking_date matches both physical rows. The immediacy filter keeps id 11, so the numerator gains +1 for a patient who contributes 1 to the denominator — internally consistent here. The real damage appears when both same-day rows are immediate: the numerator counts a single patient twice, and you can produce a percentage above 100. The denominator (distinct patients) and the numerator (matched rows) are then computed over different grains.

The robust fix is to keep the numerator on the same grain as the denominator — one decision per patient — by collapsing the immediacy test into the grouped CTE itself rather than re-joining:

WITH FirstAppointments AS (
    SELECT patient_id,
           MIN(booking_date) AS first_booking_date,
           -- among rows on the earliest date, was ANY of them immediate?
           MAX(CASE WHEN booking_date = patient_pref_date THEN 1 ELSE 0 END)
             AS immediate_flag
    FROM   Appointments
    GROUP  BY patient_id
)
-- NOTE: still imperfect if a patient has rows on the same earliest date;
-- the CASE sees ALL rows, not just earliest-date rows.
SELECT ...;

The truly grain-safe pattern is a window function that ranks rows within each patient and isolates exactly one first row before any aggregation:

WITH ranked AS (
    SELECT patient_id, booking_date, patient_pref_date,
           ROW_NUMBER() OVER (
             PARTITION BY patient_id
             ORDER BY booking_date, appointment_id   -- tiebreak on the PK
           ) AS rn
    FROM Appointments
)
SELECT ROUND(
         AVG(CASE WHEN booking_date = patient_pref_date THEN 1 ELSE 0 END) * 100,
         2) AS immediate_percentage
FROM ranked
WHERE rn = 1;

ROW_NUMBER() with an appointment_id tiebreaker guarantees exactly one row per patient even under same-day ties, so numerator and denominator share one grain by construction. AVG(0/1) * 100 is the percentage directly — no separate counts to keep in sync.

Pitfalls

• Date-join row fan-out (the headline bug): joining the first-appointment CTE back on booking_date = first_booking_date multiplies rows whenever a patient has two appointments on their earliest date. The primary key being unique does not prevent it. Numerator and denominator drift to different grains; the percentage can exceed 100.
• Assuming integer division truncates: in MySQL / yields DECIMAL, so 2/4 = 0.5 — correct. But porting this to Postgres or SQLite where 2/4 = 0 (integer division) silently returns 0.00. Force a decimal context: 100.0 * or CAST(... AS DECIMAL).
• Empty table: the denominator COUNT(*) is 0, and division by zero returns NULL in MySQL rather than erroring. The output is one row of NULL, which may not match an expected 0.00. Guard with NULLIF or accept NULL per the spec.
• Trusting the visual order of input rows: in the example, patient 40's rows appear as 07-22 then 07-18 — the first appointment is the later-listed row. MIN ignores row order, but a hand-trace that reads top-to-bottom will mislabel it.

Takeaways

• A "percentage of X among Y" problem is one ratio over a single derived set; build that set once, then count it two compatible ways.
• Re-joining a grouped CTE on a non-unique key (a date) re-expands rows — match on row identity or, better, pick the row before grouping with ROW_NUMBER().
• Know your engine's division semantics: MySQL / is decimal, Postgres/SQLite integer / truncates. AVG(CASE ... 1 ELSE 0) * 100 sidesteps both the division-grain and truncation traps in one expression.

Based on the LeetCode-style problem "Immediate Food Delivery / Same-Day Appointment" family (LeetCode 1174 lineage) and the MySQL 8.0 Reference Manual on arithmetic operators (decimal semantics of /), ROUND(), and window functions (ROW_NUMBER() OVER). Grain-safety pattern drawn from standard SQL practice. Re-authored and deepened for this guide: replaced the date-join solution's clause narration with a mechanism-first ratio model, added the same-earliest-date fan-out failure mode the original omitted, a grain-safe ROW_NUMBER() rewrite, and cross-engine division caveats.