Why do draft bookings make cancellation rates look lower?

Prepare the diagnostic input

The fixture defines a cancellation rate among completed and cancelled bookings, not all request records. Drafts never reached that cohort. Define both numerator and denominator from that policy; a floating-point percentage cannot repair the wrong inclusion set.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE bookings(id TEXT PRIMARY KEY,status TEXT);
INSERT INTO bookings VALUES ('B1','cancelled'),('B2','completed'),('B3','completed'),('B4','draft');

Reproduce the misleading result and correct it

SELECT ROUND(100.0*SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END)/COUNT(*),2) AS cancellation_pct FROM bookings;

SELECT ROUND(100.0*SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END)
/NULLIF(SUM(CASE WHEN status IN ('cancelled','completed') THEN 1 ELSE 0 END),0),2) AS cancellation_pct FROM bookings;

One of three eligible bookings is cancelled, producing 33.33%. Including the draft makes the misleading rate 25%. These are rates for an invented cohort, not a service-performance benchmark or a claim about customer behaviour.

Check the boundary before applying the fix

Change every row to draft. The eligible denominator becomes zero, so NULL represents no defined rate; reporting 0% would imply a measured absence of cancellations.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

UPDATE bookings SET status='draft';

The policy excludes active future bookings. A cohort by scheduled month or booking date must be defined separately, including still-open outcomes.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.