00:00:00

Description

Editorial

Employee Shift Overlap Intensity Audit

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

EmployeeShifts =

[{"employee_id":3,"start_time":"2024-10-01 09:00:00","end_time":"2024-10-01 17:00:00"},{"employee_id":2,"start_time":"2024-10-01 11:00:00","end_time":"2024-10-01 19:00:00"},{"employee_id":1,"start_time":"2024-10-01 15:00:00","end_time":"2024-10-01 23:00:00"},{"employee_id":2,"start_time":"2024-10-01 09:00:00","end_time":"2024-10-01 17:00:00"},{"employee_id":1,"start_time":"2024-10-01 09:00:00","end_time":"2024-10-01 17:00:00"},{"employee_id":1,"start_time":"2024-10-01 16:00:00","end_time":"2024-10-02 00:00:00"}]

Loading problem...

101

00:00:00

Description

Editorial

Employee Shift Overlap Intensity Audit

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

EmployeeShifts =

Employee Shift Overlap Intensity Audit

Hints

Employee Shift Overlap Intensity Audit

Hints