Loading problem...
A payroll analytics team tracks expected monthly work commitment per employee and compares it against actual attendance sessions.
Table: Employees
Table: Logs
Computation rules:
Task: Return the employee_id values for all employees who should be deducted.
Supported submission environments:
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 |[
{"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.
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 |[
{"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.
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 |[
{"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