Double-Booked Self-Drive Rental Cars

Hard
321.

Double-Booked Self-Drive Rental Cars

HardJoinsDates

MySQL

A self-drive car rental app found that a sync bug let two customers book the same car for overlapping times. Two confirmed bookings of one car overlap when each starts before the other ends; a booking that starts exactly when another ends is a clean handover, not an overlap. Cancelled bookings never clash.

Return every clashing pair once, with vehicle_id, booking_a and booking_b (the two booking ids, booking_a < booking_b) and overlap_minutes (the length of the shared time). Order by vehicle_id, booking_a, booking_b.

Tables

Table: RentalBooking

ColumnTypeValues
booking_idint
vehicle_idint
customer_idint
start_atdatetime
end_atdatetime
statusenumconfirmed, cancelled

Primary key: booking_id.

end_at is always after start_at.

Example

The rows must come back in the order shown.

RentalBooking

booking_idvehicle_idcustomer_idstart_atend_atstatus
1509012024-12-20 09:00:002024-12-20 18:00:00confirmed
2509022024-12-20 15:00:002024-12-21 10:00:00confirmed
3509032024-12-21 10:00:002024-12-21 20:00:00confirmed
4519042024-12-20 08:00:002024-12-22 08:00:00confirmed
5519052024-12-21 12:00:002024-12-21 14:30:00confirmed
6519062024-12-21 13:00:002024-12-21 16:00:00cancelled
7529072024-12-20 10:00:002024-12-20 12:00:00confirmed

Output

vehicle_idbooking_abooking_boverlap_minutes
5012180
5145150
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Double-Booked Self-Drive Rental Cars — SQL Joins Problem

A self-drive car rental app found that a sync bug let two customers book the same car for overlapping times.

  • Difficulty: Hard
  • Topics: Joins, Dates
  • Dialect: MySQL
  • Problem: #321

Problem statement

A self-drive car rental app found that a sync bug let two customers book the same car for overlapping times. Two confirmed bookings of one car overlap when each starts before the other ends; a booking that starts exactly when another ends is a clean handover, not an overlap. Cancelled bookings never clash.

Return every clashing pair once, with vehicle_id, booking_a and booking_b (the two booking ids, booking_a < booking_b) and overlap_minutes (the length of the shared time). Order by vehicle_id, booking_a, booking_b.

Tables

Table: RentalBooking

ColumnType
booking_idint
vehicle_idint
customer_idint
start_atdatetime
end_atdatetime
statusenum(confirmed, cancelled)

Primary key: booking_id.

end_at is always after start_at.

Examples

Example 1

RentalBooking

booking_idvehicle_idcustomer_idstart_atend_atstatus
1509012024-12-20 09:00:002024-12-20 18:00:00confirmed
2509022024-12-20 15:00:002024-12-21 10:00:00confirmed
3509032024-12-21 10:00:002024-12-21 20:00:00confirmed
4519042024-12-20 08:00:002024-12-22 08:00:00confirmed
5519052024-12-21 12:00:002024-12-21 14:30:00confirmed
6519062024-12-21 13:00:002024-12-21 16:00:00cancelled
7529072024-12-20 10:00:002024-12-20 12:00:00confirmed

Output

vehicle_idbooking_abooking_boverlap_minutes
5012180
5145150

How to solve Double-Booked Self-Drive Rental Cars

Overlap questions pair rows of one table, so join RentalBooking to itself on the same vehicle_id. The condition a.booking_id < b.booking_id does double duty: it never pairs a booking with itself and it reports each clashing pair once instead of twice.

The overlap test is the one to remember: intervals [s1, e1) and [s2, e2) overlap exactly when s1 < e2 AND s2 < e1. It covers every shape — partial overlap, one booking inside another, identical times — and the strict < makes a handover (one ends at 10:00, the next starts at 10:00) not a clash. The negated form NOT (e1 <= s2 OR e2 <= s1) says "neither is entirely before the other", which is the same thing.

The shared stretch starts at the later of the two starts and ends at the earlier of the two ends: GREATEST(start…) and LEAST(end…), or the equivalent CASE expressions, measured with TIMESTAMPDIFF(MINUTE, …). Both status filters must be applied to both sides. The self join is quadratic per car; sorting each car's bookings by start and comparing neighbours would scale better for large fleets.

Reference solution (MySQL)

SELECT a.vehicle_id, a.booking_id AS booking_a, b.booking_id AS booking_b,
       TIMESTAMPDIFF(MINUTE, GREATEST(a.start_at, b.start_at), LEAST(a.end_at, b.end_at)) AS overlap_minutes
FROM RentalBooking a
JOIN RentalBooking b
  ON b.vehicle_id = a.vehicle_id
 AND a.booking_id < b.booking_id
 AND a.start_at < b.end_at
 AND b.start_at < a.end_at
WHERE a.status = 'confirmed' AND b.status = 'confirmed'
ORDER BY a.vehicle_id, booking_a, booking_b

Another way

WITH c AS (SELECT booking_id, vehicle_id, start_at, end_at FROM RentalBooking WHERE status <> 'cancelled')
SELECT a.vehicle_id, a.booking_id AS booking_a, b.booking_id AS booking_b,
       TIMESTAMPDIFF(MINUTE,
         CASE WHEN a.start_at > b.start_at THEN a.start_at ELSE b.start_at END,
         CASE WHEN a.end_at < b.end_at THEN a.end_at ELSE b.end_at END) AS overlap_minutes
FROM c a CROSS JOIN c b
WHERE a.vehicle_id = b.vehicle_id AND a.booking_id < b.booking_id
  AND NOT (a.end_at <= b.start_at OR b.end_at <= a.start_at)
ORDER BY a.vehicle_id, a.booking_id, b.booking_id

← Parcel Delivery Funnel and First-Attempt Rate by City · Daily Sorting-Hub Backlog Including Days With None →