SQL data engineering interview problem. Difficulty: advanced. Pattern: Self Joins. About 18 minutes. Part of the Pro drill bank.
From meetings, find pairs that overlap in the same room_id. Two meetings overlap when a.start_time < b.end_time AND b.start_time < a.end_time. Return meeting_1, meeting_2 (meeting_ids with meeting_1 < meeting_2), room_id. Order by room_id, meeting_1, meeting_2.
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
Interview-style drill: Find overlapping meeting pairs in the same room.
From `meetings`, find pairs that overlap in the same `room_id`. Two meetings overlap when `a.start_time < b.end_time AND b.start_time < a.end_time`. Return `meeting_1`, `meeting_2` (meeting_ids with meeting_1 < meeting_2), `room_id`. Order by `room_id`, `meeting_1`, `meeting_2`.