Employees Below Monthly Required Hours

MEDIUM20 pts

A payroll analytics team tracks expected monthly work commitment per employee and compares it against actual attendance sessions.

Table: Employees

employee_id: unique employee identifier
needed_hours: minimum hours the employee must complete in the month

Table: Logs

employee_id: employee identifier for a work session
in_time: session start timestamp
out_time: session end timestamp

Computation rules:

Every Logs row is one session.
Session duration is measured in seconds, then converted to minutes using ceiling: session_minutes = CEIL(session_seconds / 60)
Employee worked minutes is the sum of session_minutes across all their sessions.
An employee is payroll-deducted when: worked_minutes < needed_hours * 60
Employees with no sessions have worked_minutes = 0.

Task: Return the employee_id values for all employees who should be deducted.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Employees:
| employee_id | needed_hours |
|-------------|--------------|
| 1           | 20           |
| 2           | 12           |
| 3           | 2            |

Logs:
| employee_id | in_time             | out_time            |
|-------------|---------------------|---------------------|
| 1           | 2022-10-01 09:00:00 | 2022-10-01 17:00:00 |
| 1           | 2022-10-06 09:05:04 | 2022-10-06 17:09:03 |
| 1           | 2022-10-12 23:00:00 | 2022-10-13 03:00:01 |
| 2           | 2022-10-29 12:00:00 | 2022-10-29 23:58:58 |

Output

[
  {"employee_id":2},
  {"employee_id":3}
]

Explanation

Employee 1 exceeds 20 hours after per-session minute ceiling. Employee 2 misses 12 hours by one minute. Employee 3 has no sessions and is deducted.

Example

Input

Employees:
| employee_id | needed_hours |
|-------------|--------------|
| 10          | 2            |
| 20          | 0            |
| 30          | 2            |

Logs:
| employee_id | in_time             | out_time            |
|-------------|---------------------|---------------------|
| 10          | 2022-10-10 08:00:00 | 2022-10-10 08:59:59 |
| 30          | 2022-10-10 23:30:00 | 2022-10-11 01:29:30 |

Output

[
  {"employee_id":10}
]

Explanation

Employee 10 records 3599 seconds, which rounds to 60 minutes and is below the required 120 minutes. Employee 30 rounds to 120 minutes and meets the requirement. Employee 20 requires zero hours, so only employee 10 is deducted.

Example

Input

Employees:
| employee_id | needed_hours |
|-------------|--------------|
| 100         | 3            |
| 200         | 4            |

Logs:
| employee_id | in_time             | out_time            |
|-------------|---------------------|---------------------|
| 100         | 2022-10-20 09:00:00 | 2022-10-20 10:00:01 |
| 100         | 2022-10-20 11:00:00 | 2022-10-20 12:00:01 |
| 100         | 2022-10-20 13:00:00 | 2022-10-20 14:00:01 |
| 200         | 2022-10-20 09:00:00 | 2022-10-20 11:00:00 |
| 200         | 2022-10-20 12:00:00 | 2022-10-20 13:00:00 |

Output

[
  {"employee_id":200}
]

Explanation

Employee 100 has three sessions of 3601 seconds, each rounding to 61 minutes, so total worked minutes are 183 and they pass a 3-hour target. Employee 200 works exactly 180 minutes but needs 240 minutes, so employee 200 is deducted.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Employees <= 5000
0 <= number of rows in Logs <= 30000
employee_id is unique in Employees
1 <= employee_id <= 2000000000
0 <= needed_hours <= 744
(employee_id, in_time, out_time) is unique in Logs
Every log has in_time < out_time
in_time occurs in October 2022
out_time is at most one day after in_time
Session minutes are rounded up individually before summation
Employees with no logs count as 0 worked minutes
Return exactly one column: employee_id
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named employee_id

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Logs =

[{"employee_id":1,"in_time":"2022-10-01 09:00:00","out_time":"2022-10-01 17:00:00"},{"employee_id":1,"in_time":"2022-10-06 09:05:04","out_time":"2022-10-06 17:09:03"},{"employee_id":1,"in_time":"2022-10-12 23:00:00","out_time":"2022-10-13 03:00:01"},{"employee_id":2,"in_time":"2022-10-29 12:00:00","out_time":"2022-10-29 23:58:58"}]

Employees =

[{"employee_id":1,"needed_hours":20},{"employee_id":2,"needed_hours":12},{"employee_id":3,"needed_hours":2}]

Employees Below Monthly Required Hours

Hints

Employees Below Monthly Required Hours

Hints