Find Overlapping Shifts - Problem

You are given a table EmployeeShifts containing information about employee work shifts with start and end times.

Your task: Count the number of overlapping shifts for each employee. Two shifts are considered overlapping if one shift's end_time is later than another shift's start_time.

Return the result table ordered by employee_id in ascending order.

Table Schema

EmployeeShifts
Column Name Type Description
employee_id PK int ID of the employee
start_time PK time Start time of the shift
end_time time End time of the shift
Primary Key: (employee_id, start_time)
Note: Each row represents a work shift for an employee on a specific date

Input & Output

Example 1 — Basic Overlapping Shifts
Input Table:
employee_id start_time end_time
1 09:00:00 12:00:00
1 11:00:00 14:00:00
2 10:00:00 13:00:00
Output:
employee_id overlapping_shifts
1 1
💡 Note:

Employee 1 has two shifts: 09:00-12:00 and 11:00-14:00. These shifts overlap because the first shift ends at 12:00, which is after the second shift starts at 11:00. Employee 2 has only one shift, so no overlaps are possible.
Example 2 — Multiple Overlapping Shifts
Input Table:
employee_id start_time end_time
3 08:00:00 11:00:00
3 09:00:00 12:00:00
3 10:00:00 13:00:00
Output:
employee_id overlapping_shifts
3 2
💡 Note:

Employee 3 has three shifts with multiple overlaps: shift 1 (08:00-11:00) overlaps with shift 2 (09:00-12:00), and shift 2 overlaps with shift 3 (10:00-13:00). This results in 2 overlapping pairs.
Example 3 — No Overlapping Shifts
Input Table:
employee_id start_time end_time
4 08:00:00 10:00:00
4 11:00:00 13:00:00
4 14:00:00 16:00:00
Output:
employee_id overlapping_shifts
💡 Note:

Employee 4 has three shifts but none overlap - each shift ends before the next one starts. Since there are no overlapping shifts, this employee doesn't appear in the result.

Constraints

  • 1 ≤ employee_id ≤ 1000
  • start_time and end_time are valid time values
  • start_time < end_time for all shifts

Visualization

Tap to expand
Find Overlapping Shifts INPUT emp_id shift_id start end 1 A 08:00 12:00 1 B 10:00 14:00 1 C 15:00 18:00 2 D 09:00 11:00 2 E 10:30 13:00 Employee 1 Timeline A: 8-12 B: 10-14 C: 15-18 OVERLAP! ALGORITHM STEPS 1 Self-Join Table Join shifts with itself on same employee_id 2 Filter Different Shifts WHERE s1.shift_id < s2.shift_id 3 Check Overlap s1.end_time > s2.start_time 4 Group & Count GROUP BY employee_id COUNT(*) overlaps SELECT s1.emp_id, COUNT (*) overlaps FROM Shifts s1, s2 WHERE s1.end > s2.start FINAL RESULT employee_id overlaps 1 1 2 1 Overlap Analysis Employee 1 Shift A (8-12) overlaps with Shift B (10-14) Count: 1 Employee 2 Shift D (9-11) overlaps with Shift E (10:30-13) Count: 1 Key Insight: Two shifts overlap when: end_time of shift1 > start_time of shift2. Using a self-join with shift_id comparison (s1.id < s2.id) prevents counting the same pair twice and self-comparison. Time complexity: O(n^2) for self-join. Optimal for SQL-based overlap detection per employee. TutorialsPoint - Find Overlapping Shifts | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 18
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen