SQL interview question: Find Overlapping Date Ranges
Detect Overlapping Room Bookings
The booking system needs to prevent double-bookings by detecting when room reservations overlap.
Find all pairs of reservations for the same room where the date ranges overlap (indicating a conflict).
Return room_id, reservation_1 (id), guest_1, reservation_2 (id), and guest_2. List each conflicting pair once, with the lower reservation id as reservation_1.
Overlap formula: check_in_1 < check_out_2 AND check_in_2 < check_out_1
Skills: self-join with complex date range overlap logic
Tables
reservations 5 rows
- reservation_id integer PK
- room_id integer
- guest_name text
- check_in text
- check_out text
| reservation_id | room_id | guest_name | check_in | check_out |
|---|---|---|---|---|
| 1 | 101 | Alice | 2025-01-10 | 2025-01-15 |
| 2 | 101 | Bob | 2025-01-13 | 2025-01-18 |
| 3 | 101 | Carol | 2025-01-20 | 2025-01-25 |