Employees Below Monthly Required Hours

Medium

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 1

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 2

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 3

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.

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

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 |

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 |

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 |