Overlapping Room Bookings
Hard · Joins
Two date ranges overlap when each one starts before the other ends: a.check_in < b.check_out AND b.check_in < a.check_out. A self join compares every booking with every other booking. Use a.id < b.id so each pair appears once. PostgreSQL also has range types: daterange(check_in, check_out) && daterange(...) tests the same thing.
A guest leaves on the morning of check_out, so a booking that checks in on the day another one checks out does not overlap it. Find every pair of bookings of the same room that overlap.
Return room, first_booking (the smaller id), second_booking (the larger id) and overlap_nights (how many nights both bookings share, as an integer), sorted by room, first_booking, then second_booking.