Date Range Overlap — Solution & ExplanationProblems / Date Range Overlap / EditorialApproach

Self-join: Join the reservations table to itself on the same room_id, with r1.reservation_id < r2.reservation_id to ensure each pair appears only once.

Overlap condition: Two intervals [a, b) and [c, d) overlap when a < d AND c < b.

Overlap boundaries: GREATEST(r1.check_in, r2.check_in) gives the start of overlap; LEAST(r1.check_out, r2.check_out) gives the end.

Duration: DATEDIFF('day', overlap_start, overlap_end) computes the overlap length.

Key ConceptInterval overlap detection via self-join is fundamental in scheduling, booking, and resource allocation systems. The canonical overlap test start1 < end2 AND start2 < end1 is a pattern worth memorizing.Hints

Two date ranges overlap when start1 < end2 AND start2 < end1.

Use a self-join with reservation_id_1 < reservation_id_2 to avoid duplicate pairs.

GREATEST and LEAST compute the overlap boundaries.

Solve Date Range Overlap yourself →