Overlapping meetings in a room

SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 18 minutes. Part of the Pro drill bank.

A company books meeting rooms and wants to find double bookings, which means two meetings in the same room that overlap in time. From meetings, find pairs of meetings in the same room_id that overlap. Two meetings overlap when a.start_time < b.end_time AND b.start_time < a.end_time. Return meeting_1 and meeting_2 (the ids, with meeting_1 < meeting_2 so each pair appears once) and room_id. Sort by room_id, meeting_1, meeting_2.

Requirements

• Compare meetings with each other inside the same room.
• Keep pairs that overlap, using the formula in the task.
• List each pair once, with the smaller meeting id first.
• Return meeting_1, meeting_2 and room_id, sorted by room_id, meeting_1, meeting_2.

Constraints

• The table is meetings, joined to itself with two aliases.
• Overlap formula: a.start_time < b.end_time AND b.start_time < a.end_time.
• Use a.meeting_id < b.meeting_id so that a meeting is not paired with itself and each pair is not listed twice.

Examples

Input: meetings (Room A slice) meeting_id | room_id | start_time | end_time 1 | A | 2024-01-01 09:00:00 | 2024-01-01 10:00:00 2 | A | 2024-01-01 09:30:00 | 2024-01-01 10:30:00 3 | A | 2024-01-01 11:00:00 | 2024-01-01 12:00:00 Output: meeting_1 | meeting_2 | room_id 1 | 2 | A Why this passes: Meetings 1 and 2 overlap; meeting 3 starts after 2 ends so it is not paired.

Topics: lakebench, sql, overlaps, intervals.

More SQL interview questions · All interview problems · Learn data engineering