Opening drill
Interview question
Detect overlapping bookings for a resource
Implements correct half-open interval overlap logic for booking validation.
TL;DR
Implements correct half-open interval overlap logic for booking validation.
Range overlap logic, boundary semantics, cancellation filtering, update exclusions, and concurrency awareness.
Practice the problem like a real interview: restate, reason, implement, and test.
Table: Bookings(BookingId, ResourceId, StartsAt, EndsAt, Status). Parameters describe a proposed [StartsAt, EndsAt) booking and may include @ExistingBookingId during an edit. Return conflicting active bookings for the same resource. A booking ending exactly when another starts is allowed.
Next in SQL Practice: Filter Without Duplicates
An existing booking is [10:00, 11:00). Proposals [10:30, 11:30) and [09:30, 10:30) conflict. [11:00, 12:00) does not.
@StartsAt >= @EndsAt before using this query.Two half-open intervals overlap when each starts before the other ends. That becomes existing.StartsAt < proposed.EndsAt and existing.EndsAt > proposed.StartsAt. I keep both comparisons strict so touching boundaries are allowed. The query detects a conflict, but preventing races also requires the write path to serialize competing reservations or enforce the rule through a suitable database design.
SELECT b.BookingId, b.StartsAt, b.EndsAt
FROM dbo.Bookings AS b
WHERE b.ResourceId = @ResourceId
AND b.Status IN ('Pending', 'Confirmed')
AND b.StartsAt < @EndsAt
AND b.EndsAt > @StartsAt
AND (@ExistingBookingId IS NULL OR b.BookingId <> @ExistingBookingId)
ORDER BY b.StartsAt, b.BookingId;
An index beginning with ResourceId and including the time and status columns narrows the search, although two-sided interval predicates cannot usually seek perfectly on both boundaries. For dense booking calendars, a slot model or stronger reservation strategy may be easier to enforce.
BETWEEN and rejecting bookings that merely touch.Share the question with a concise Core-answer excerpt and invite other developers to add their perspective.
Help improve the interview library.
Say thanks with a standalone, one-time $5 contribution. No account required.
Aporeon is shaped by Aleksandar Tomovski, a software developer with experience on both sides of technical interviews. Content is reviewed for accuracy, natural spoken delivery, useful depth, and honest trade-offs.