Which waiting party could fit the remaining session seats?

Define the decision and input records

For a shared session, remaining seats are room capacity less already confirmed seats. This query skips an oversized party and proposes the earliest eligible party, ordered by registration time then ID. That is a policy choice: strict first-in-first-out would stop at the first oversized party instead.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE rooms(id TEXT PRIMARY KEY, capacity INTEGER);
CREATE TABLE bookings(id TEXT PRIMARY KEY, room_id TEXT, starts_at TEXT, ends_at TEXT, seats INTEGER, status TEXT);
CREATE TABLE waitlist(id TEXT PRIMARY KEY, booking_id TEXT, requested_seats INTEGER, joined_at TEXT);
INSERT INTO rooms VALUES ('R1',8),('R2',4);
INSERT INTO bookings VALUES ('B1','R1','2026-10-10 09:00','2026-10-10 10:00',5,'confirmed'),('B2','R1','2026-10-10 09:30','2026-10-10 10:30',4,'confirmed'),('B3','R1','2026-10-10 10:30','2026-10-10 11:30',2,'confirmed'),('B4','R2','2026-10-10 12:00','2026-10-10 13:00',5,'confirmed'),('B5','R2','2026-10-10 12:00','2026-10-10 13:00',1,'cancelled');
INSERT INTO waitlist VALUES ('W1','B3',7,'2026-10-08 09:00'),('W2','B3',2,'2026-10-08 10:00'),('W3','B3',1,'2026-10-08 11:00');

Run the query and compare the result

SELECT w.id,w.requested_seats,r.capacity-b.seats AS free_seats
FROM waitlist w JOIN bookings b ON b.id=w.booking_id
JOIN rooms r ON r.id=b.room_id
WHERE b.id='B3' AND b.status='confirmed'
 AND w.requested_seats<=r.capacity-b.seats
ORDER BY w.joined_at,w.id LIMIT 1;

B3 has six free seats. W1 requests seven and cannot fit; W2 requests two and is the first fitting candidate. W3 fits as well but joined later. Showing W2 is a proposal only; reserving seats needs a fresh capacity check within the booking transaction.

Apply the result within its limits

The sample proposes one party and does not optimise utilisation or promote several parties. It assumes B3 is a shared session whose seats belong to that session, unlike the exclusive-room conflict scenario. No notification is sent.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.