Shift Collision Audit

Medium

A workforce analytics team needs a collision counter for employee shifts on a single day. Table: EmployeeShifts employee_id: employee identifier start_time: shift start time in HH:MM:SS end_time: shift end time in HH:MM:SS Rules: (employee_id, start_time) is unique. Every shift has start_time < end_time. For each employee, count overlapping ordered shift pairs (A, B) such that: A.start_time < B.start_time A.end_time > B.start_time Interpretation notes: The strict A.start_time < B.start_time condition prevents counting the same pair twice. If A.end_time == B.start_time, the pair is NOT overlapping. Output requirements: Return only employees with at least one overlapping pair. Return exactly columns in this order: employee_id, overlapping_shifts Sort by employee_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

EmployeeShifts:
| employee_id | start_time | end_time |
|-------------|------------|----------|
| 1           | 08:00:00   | 12:00:00 |
| 1           | 11:00:00   | 15:00:00 |
| 1           | 14:00:00   | 18:00:00 |
| 2           | 09:00:00   | 17:00:00 |
| 2           | 16:00:00   | 20:00:00 |
| 3           | 10:00:00   | 12:00:00 |
| 3           | 13:00:00   | 15:00:00 |
| 3           | 16:00:00   | 18:00:00 |
| 4           | 08:00:00   | 10:00:00 |
| 4           | 09:00:00   | 11:00:00 |

Output

[
  {"employee_id":1,"overlapping_shifts":2},
  {"employee_id":2,"overlapping_shifts":1},
  {"employee_id":4,"overlapping_shifts":1}
]

Explanation

Employee 1 has two overlapping ordered pairs, employee 2 has one, employee 3 has zero, and employee 4 has one.

Example 2

Input

EmployeeShifts:
| employee_id | start_time | end_time |
|-------------|------------|----------|
| 10          | 08:00:00   | 10:00:00 |
| 10          | 10:00:00   | 12:00:00 |
| 10          | 11:00:00   | 13:00:00 |
| 11          | 09:00:00   | 12:00:00 |
| 11          | 09:30:00   | 10:30:00 |
| 11          | 10:30:00   | 11:30:00 |

Output

[
  {"employee_id":10,"overlapping_shifts":1},
  {"employee_id":11,"overlapping_shifts":2}
]

Explanation

Boundary-touching shifts (end == next start) are not overlaps, but true strict-overlap pairs are counted.

Example 3

Input

EmployeeShifts:
| employee_id | start_time | end_time |
|-------------|------------|----------|
| 22          | 05:00:00   | 23:00:00 |
| 22          | 06:00:00   | 22:00:00 |
| 22          | 07:00:00   | 21:00:00 |
| 22          | 08:00:00   | 20:00:00 |
| 21          | 06:00:00   | 07:00:00 |
| 21          | 07:00:00   | 08:00:00 |

Output

[
  {"employee_id":22,"overlapping_shifts":6}
]

Explanation

Employee 22 has 4 fully nested shifts, so every ordered pair overlaps. Employee 21 has touching shifts and is excluded.

Constraints

1 <= number of rows in EmployeeShifts <= 250000
1 <= employee_id <= 1000000000
(employee_id, start_time) is unique in each test case
start_time and end_time are valid HH:MM:SS values
For every row: start_time < end_time
A pair contributes only if earlier start shift has end_time > later start shift
Pairs with end_time == later start_time are not overlaps
Return only employees with overlapping_shifts > 0
Return exactly columns: employee_id, overlapping_shifts
Sort by employee_id ascending
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with exact required columns

EmployeeShifts: | employee_id | start_time | end_time | |-------------|------------|----------| | 1 | 08:00:00 | 12:00:00 | | 1 | 11:00:00 | 15:00:00 | | 1 | 14:00:00 | 18:00:00 | | 2 | 09:00:00 | 17:00:00 | | 2 | 16:00:00 | 20:00:00 | | 3 | 10:00:00 | 12:00:00 | | 3 | 13:00:00 | 15:00:00 | | 3 | 16:00:00 | 18:00:00 | | 4 | 08:00:00 | 10:00:00 | | 4 | 09:00:00 | 11:00:00 |

EmployeeShifts: | employee_id | start_time | end_time | |-------------|------------|----------| | 10 | 08:00:00 | 10:00:00 | | 10 | 10:00:00 | 12:00:00 | | 10 | 11:00:00 | 13:00:00 | | 11 | 09:00:00 | 12:00:00 | | 11 | 09:30:00 | 10:30:00 | | 11 | 10:30:00 | 11:30:00 |

EmployeeShifts: | employee_id | start_time | end_time | |-------------|------------|----------| | 22 | 05:00:00 | 23:00:00 | | 22 | 06:00:00 | 22:00:00 | | 22 | 07:00:00 | 21:00:00 | | 22 | 08:00:00 | 20:00:00 | | 21 | 06:00:00 | 07:00:00 | | 21 | 07:00:00 | 08:00:00 |