Employee Shift Overlap Intensity Audit

Hard

A workforce analytics team wants to measure scheduling pressure from employee shift timelines. Table: EmployeeShifts employee_id: employee identifier start_time: shift start timestamp (YYYY-MM-DD HH:MM:SS) end_time: shift end timestamp (YYYY-MM-DD HH:MM:SS) The pair (employee_id, start_time) is unique. For each employee_id, compute two metrics: max_overlapping_shifts Maximum number of simultaneously active shifts at any moment. total_overlap_duration Total pairwise overlap time in minutes. If k shifts are active during a segment of d minutes, that segment contributes C(k,2) * d minutes. Same-date rule: Compare shifts only within the same calendar date bucket, where shift_date is DATE(start_time). Interval rule: Treat each shift as half-open [start_time, end_time). If one shift ends exactly when another starts, they do not overlap. Output requirements: Return exactly these columns in this order: employee_id, max_overlapping_shifts, total_overlap_duration Return rows sorted 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           | 2024-10-01 09:00:00  | 2024-10-01 17:00:00  |
| 1           | 2024-10-01 15:00:00  | 2024-10-01 23:00:00  |
| 1           | 2024-10-01 16:00:00  | 2024-10-02 00:00:00  |
| 2           | 2024-10-01 09:00:00  | 2024-10-01 17:00:00  |
| 2           | 2024-10-01 11:00:00  | 2024-10-01 19:00:00  |
| 3           | 2024-10-01 09:00:00  | 2024-10-01 17:00:00  |

Output

[
  {"employee_id":1,"max_overlapping_shifts":3,"total_overlap_duration":600},
  {"employee_id":2,"max_overlapping_shifts":2,"total_overlap_duration":360},
  {"employee_id":3,"max_overlapping_shifts":1,"total_overlap_duration":0}
]

Explanation

Employee 1 peaks at 3 simultaneous shifts and accumulates 600 pairwise-overlap minutes. Employee 2 has a single 6-hour overlap window. Employee 3 has no overlap.

Example 2

Input

EmployeeShifts:
| employee_id | start_time           | end_time             |
|-------------|----------------------|----------------------|
| 10          | 2024-01-05 22:00:00  | 2024-01-06 02:00:00  |
| 10          | 2024-01-06 00:30:00  | 2024-01-06 03:30:00  |
| 10          | 2024-01-06 08:00:00  | 2024-01-06 11:00:00  |
| 10          | 2024-01-06 10:00:00  | 2024-01-06 12:00:00  |
| 11          | 2024-01-06 07:00:00  | 2024-01-06 08:00:00  |
| 11          | 2024-01-06 08:00:00  | 2024-01-06 09:00:00  |

Output

[
  {"employee_id":10,"max_overlapping_shifts":2,"total_overlap_duration":60},
  {"employee_id":11,"max_overlapping_shifts":1,"total_overlap_duration":0}
]

Explanation

For employee 10, the cross-midnight pair is ignored by the same-date bucketing rule (different DATE(start_time) values). The 08:00-12:00 windows on 2024-01-06 overlap for 60 minutes.

Example 3

Input

EmployeeShifts:
| employee_id | start_time           | end_time             |
|-------------|----------------------|----------------------|
| 20          | 2024-03-01 08:00:00  | 2024-03-01 12:00:00  |
| 20          | 2024-03-01 09:00:00  | 2024-03-01 11:00:00  |
| 20          | 2024-03-01 09:30:00  | 2024-03-01 10:30:00  |
| 20          | 2024-03-01 10:00:00  | 2024-03-01 13:00:00  |
| 21          | 2024-03-01 23:00:00  | 2024-03-01 23:59:00  |

Output

[
  {"employee_id":20,"max_overlapping_shifts":4,"total_overlap_duration":450},
  {"employee_id":21,"max_overlapping_shifts":1,"total_overlap_duration":0}
]

Explanation

Employee 20 reaches concurrency 4 and accumulates pairwise overlap contributions across each timeline segment. Employee 21 has only one shift.

Constraints

1 <= number of rows in EmployeeShifts <= 300000
1 <= employee_id <= 2000000000
(employee_id, start_time) is unique
start_time and end_time follow YYYY-MM-DD HH:MM:SS
For every row: start_time < end_time
All generated tests use minute-level timestamps (seconds = 00)
Overlap comparison is scoped by shift_date = DATE(start_time)
Intervals are evaluated as half-open [start_time, end_time)
Return exactly columns: employee_id, max_overlapping_shifts, total_overlap_duration
Sort output by employee_id ascending
SQL submissions run on SQLite 3.27.2
Pandas submissions must return a DataFrame with exact required columns

EmployeeShifts: | employee_id | start_time | end_time | |-------------|----------------------|----------------------| | 1 | 2024-10-01 09:00:00 | 2024-10-01 17:00:00 | | 1 | 2024-10-01 15:00:00 | 2024-10-01 23:00:00 | | 1 | 2024-10-01 16:00:00 | 2024-10-02 00:00:00 | | 2 | 2024-10-01 09:00:00 | 2024-10-01 17:00:00 | | 2 | 2024-10-01 11:00:00 | 2024-10-01 19:00:00 | | 3 | 2024-10-01 09:00:00 | 2024-10-01 17:00:00 |

[ {"employee_id":1,"max_overlapping_shifts":3,"total_overlap_duration":600}, {"employee_id":2,"max_overlapping_shifts":2,"total_overlap_duration":360}, {"employee_id":3,"max_overlapping_shifts":1,"total_overlap_duration":0} ]

EmployeeShifts: | employee_id | start_time | end_time | |-------------|----------------------|----------------------| | 10 | 2024-01-05 22:00:00 | 2024-01-06 02:00:00 | | 10 | 2024-01-06 00:30:00 | 2024-01-06 03:30:00 | | 10 | 2024-01-06 08:00:00 | 2024-01-06 11:00:00 | | 10 | 2024-01-06 10:00:00 | 2024-01-06 12:00:00 | | 11 | 2024-01-06 07:00:00 | 2024-01-06 08:00:00 | | 11 | 2024-01-06 08:00:00 | 2024-01-06 09:00:00 |

EmployeeShifts: | employee_id | start_time | end_time | |-------------|----------------------|----------------------| | 20 | 2024-03-01 08:00:00 | 2024-03-01 12:00:00 | | 20 | 2024-03-01 09:00:00 | 2024-03-01 11:00:00 | | 20 | 2024-03-01 09:30:00 | 2024-03-01 10:30:00 | | 20 | 2024-03-01 10:00:00 | 2024-03-01 13:00:00 | | 21 | 2024-03-01 23:00:00 | 2024-03-01 23:59:00 |