Why do individually valid parties exceed shared-room capacity together?

Prepare the diagnostic input

This room allows several parties at once, up to eight seats in total. A per-booking check cannot see combined demand. Add seats at starts, subtract them at ends, group all changes at the same instant, then calculate running occupancy using start-inclusive/end-exclusive intervals.

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,starts_at TEXT,ends_at TEXT,seats INTEGER);
CREATE TABLE room(capacity INTEGER);
INSERT INTO room VALUES (8);
INSERT INTO bookings VALUES ('B1','2026-10-10 09:00','2026-10-10 10:00',5),('B2','2026-10-10 09:30','2026-10-10 10:30',4);

Reproduce the misleading result and correct it

SELECT b.id,b.seats FROM bookings b CROSS JOIN room r WHERE b.seats>r.capacity ORDER BY b.id;

WITH changes AS (SELECT starts_at AS at,seats AS delta FROM bookings UNION ALL SELECT ends_at,-seats FROM bookings),
net AS (SELECT at,SUM(delta) AS delta FROM changes GROUP BY at),
occupancy AS (SELECT at,SUM(delta) OVER(ORDER BY at ROWS UNBOUNDED PRECEDING) AS seats FROM net)
SELECT o.at,o.seats FROM occupancy o CROSS JOIN room r WHERE o.seats>r.capacity ORDER BY o.at;

Five-seat B1 and four-seat B2 each fit alone, but total nine at 09:30. The wrong query has no exceptions; the correction identifies that boundary. This differs from exclusive-room overlap detection: sharing is allowed, and the question is total seats.

Check the boundary before applying the fix

Move B2’s start to exactly B1’s end. Net the simultaneous departure and arrival before testing occupancy; the room never exceeds eight.

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 starts_at='2026-10-10 10:00' WHERE id='B2';

The fixture has one room and positive-length accepted bookings only. Invalid intervals, cancellations and multiple resources must be validated or partitioned before the sweep.

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.