Double-booked meeting rooms

A facilities team needs to find meeting rooms that were booked by two people at the same time. Two bookings overlap only when they are in the same room and each starts before the other ends. A booking that starts exactly when another ends is back-to-back, not a clash.

From room_bookings, return one row per clashing pair: booking_a, booking_b (with booking_a < booking_b), room_id, overlap_minutes and overlap_type, where overlap_type is CONTAINED if one booking fits entirely inside the other and PARTIAL otherwise. Order by room_id, booking_a, booking_b.

1. Step 1: Self-join the table on the same `room_id` and use `a.booking_id < b.booking_id` so each pair appears once.
2. Step 2: The standard overlap test is `a.start < b.end AND b.start < a.end`. Using strict inequalities means a booking that starts exactly when another ends (R1 10:30, R3 12:00) is not a clash.
3. Step 3: The overlapping window runs from `GREATEST(start times)` to `LEAST(end times)`; its length in minutes is the overlap.
4. Step 4: A pair is CONTAINED when one booking's interval lies inside the other's, otherwise PARTIAL. Booking 10 is on a different day, so it never overlaps.

SELECT a.booking_id AS booking_a,
       b.booking_id AS booking_b,
       a.room_id,
       ROUND(EXTRACT(EPOCH FROM LEAST(a.end_time, b.end_time)
                              - GREATEST(a.start_time, b.start_time)) / 60)::int AS overlap_minutes,
       CASE
         WHEN (a.start_time >= b.start_time AND a.end_time <= b.end_time)
           OR (b.start_time >= a.start_time AND b.end_time <= a.end_time)
         THEN 'CONTAINED'
         ELSE 'PARTIAL'
       END AS overlap_type
FROM room_bookings a
JOIN room_bookings b
  ON a.room_id = b.room_id
 AND a.booking_id < b.booking_id
 AND a.start_time < b.end_time
 AND b.start_time < a.end_time
ORDER BY a.room_id, a.booking_id, b.booking_id;