Double-booked rooms

Medium

Question

The bookings table has one row for each stay in a hotel room: the room, the check_in day and the check_out day. The guest leaves on the morning of check_out, so another guest can check in on that same day.

Two bookings of the same room overlap when the guests would be in the room on the same night. A stay that ends on the day another stay starts does not overlap. A short stay inside a long stay does overlap.

Return every pair of overlapping bookings as booking_a, booking_b (the two booking ids, with the smaller id in booking_a) and room. Each pair must appear once. Sort by room, then booking_a, then booking_b.

The order of the rows matters for this question.

self join
interval overlap
join
date
pairs

Read first: Lesson 44, SQL joins: inner, left, anti-join and self-join

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

bookings5 columns · 8 rowsnot run yet

CREATE TABLE bookings (id INTEGER PRIMARY KEY, room INTEGER NOT NULL, guest TEXT NOT NULL, check_in TEXT NOT NULL, check_out TEXT NOT NULL);

Example rows of the bookings table
id	room	guest	check_in	check_out
1	101	Anil	2026-03-01	2026-03-05
2	101	Bela	2026-03-05	2026-03-08
3	101	Charu	2026-03-04	2026-03-06
4	102	Dev	2026-03-10	2026-03-20
5	102	Esha	2026-03-12	2026-03-14
6	103	Farid	2026-03-10	2026-03-12
7	103	Gauri	2026-03-12	2026-03-15
8	104	Hari	2026-03-10	2026-03-20

About this SQL

Dates are text in the form YYYY-MM-DD in SQLite, and they compare in date order, so < and > work on them. Two ranges [s1, e1) and [s2, e2) overlap when s1 < e2 AND s2 < e1. This one test covers all cases (partly inside, fully inside, same range). Using < and not <= is what makes a stay that ends when another starts a non-overlap.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready