Overlapping Driver Deliveries
A dispatcher has accidentally booked some drivers on overlapping delivery slots. Two deliveries of the same driver overlap when one starts strictly before the other ends and vice versa. Slots that only touch (one ends exactly when the next starts) do not overlap.
List every overlapping pair once, with the smaller delivery_id first.
Return columns driver_id, first_delivery, second_delivery. Order by driver_id, first_delivery, then second_delivery, all ascending.
Schema
CREATE TABLE deliveries ( delivery_id INTEGER PRIMARY KEY, driver_id INTEGER NOT NULL, start_time TEXT NOT NULL, end_time TEXT NOT NULL );
Sample data
deliveries
| delivery_id | driver_id | start_time | end_time |
|---|---|---|---|
| 1 | 7 | 2026-06-01 09:00 | 2026-06-01 10:00 |
| 2 | 7 | 2026-06-01 09:30 | 2026-06-01 11:00 |
| 3 | 7 | 2026-06-01 11:00 | 2026-06-01 12:00 |
| 4 | 8 | 2026-06-01 09:00 | 2026-06-01 13:00 |
| 5 | 8 | 2026-06-01 10:00 | 2026-06-01 10:30 |
| 6 | 9 | 2026-06-01 09:00 | 2026-06-01 10:00 |
| 7 | 8 | 2026-06-01 12:59 | 2026-06-01 14:00 |
Hint 1
Two intervals [s1, e1) and [s2, e2) overlap exactly when s1 < e2 AND s2 < e1.
Hint 2
a.delivery_id < b.delivery_id keeps each pair once and stops a delivery from pairing with itself.
Good practice for: Backend Developer, Data Engineer, Database Administrator, Data Analyst
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.