00:00:00

Description

Editorial

Shift Collision Audit

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

EmployeeShifts =

[{"employee_id":1,"start_time":"08:00:00","end_time":"12:00:00"},{"employee_id":1,"start_time":"11:00:00","end_time":"15:00:00"},{"employee_id":1,"start_time":"14:00:00","end_time":"18:00:00"},{"employee_id":2,"start_time":"09:00:00","end_time":"17:00:00"},{"employee_id":2,"start_time":"16:00:00","end_time":"20:00:00"},{"employee_id":3,"start_time":"10:00:00","end_time":"12:00:00"},{"employee_id":3,"start_time":"13:00:00","end_time":"15:00:00"},{"employee_id":3,"start_time":"16:00:00","end_time":"18:00:00"},{"employee_id":4,"start_time":"08:00:00","end_time":"10:00:00"},{"employee_id":4,"start_time":"09:00:00","end_time":"11:00:00"}]

Loading problem...

101

00:00:00

Description

Editorial

Shift Collision Audit

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

EmployeeShifts =

Shift Collision Audit

Hints

Shift Collision Audit

Hints