Detect overlapping shifts using self join
SQL
Hard
3 views
Problem Description
Shifts(emp_id, start_time, end_time). Find employees whose shifts overlap on same day.
Sample Test Case
Output:
Overlapping pairs
Constraints
Self join with time overlap condition
Official Solution
SELECT a.emp_id, a.start_time, a.end_time, b.start_time AS overlap_start, b.end_time AS overlap_end FROM Shifts a JOIN Shifts b ON a.emp_id = b.emp_id AND a.start_time < b.end_time AND b.start_time < a.end_time AND a.start_time < b.start_time;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!