Employees With Deductions - Problem
Given two tables Employees and Logs, find the IDs of employees who will have deductions from their salary due to insufficient work hours.
Each employee must work a certain number of hours every month. The actual hours worked are calculated from the Logs table by:
- Summing all work sessions for each employee
- Each session duration is rounded up to the nearest minute
- For example: 51 minutes 2 seconds becomes 52 minutes
Return the employee_id of employees whose total worked minutes is less than their required hours (converted to minutes).
Table Schema
Employees
| Column Name | Type | Description |
|---|---|---|
employee_id
PK
|
int | Unique employee identifier |
needed_hours
|
int | Minimum hours required per month |
Primary Key: employee_id
Logs
| Column Name | Type | Description |
|---|---|---|
employee_id
PK
|
int | Employee identifier |
in_time
PK
|
datetime | Work session start time |
out_time
PK
|
datetime | Work session end time |
Primary Key: (employee_id, in_time, out_time)
Input & Output
Example 1 — Mixed Work Hours
Input Tables:
Employees
| employee_id | needed_hours |
|---|---|
| 1 | 10 |
| 2 | 12 |
Logs
| employee_id | in_time | out_time |
|---|---|---|
| 1 | 2022-10-01 09:00:00 | 2022-10-01 17:30:00 |
| 1 | 2022-10-02 09:00:00 | 2022-10-02 10:30:00 |
Output:
| employee_id |
|---|
| 2 |
💡 Note:
Employee 1 worked 8.5 hours + 1.5 hours = 10 hours total, meeting the 10-hour requirement.
Employee 2 has no logged work sessions, so 0 hours < 12 hours required, resulting in deductions.
Example 2 — Minute Rounding
Input Tables:
Employees
| employee_id | needed_hours |
|---|---|
| 3 | 8 |
Logs
| employee_id | in_time | out_time |
|---|---|---|
| 3 | 2022-10-01 09:00:00 | 2022-10-01 16:59:30 |
Output:
| employee_id |
|---|
| 3 |
💡 Note:
Employee 3 worked 7 hours 59 minutes 30 seconds, which rounds up to 480 minutes (8 hours).
However, since they need exactly 8 hours and worked slightly less, they receive deductions.
Constraints
-
1 ≤ employee_id ≤ 10000 -
1 ≤ needed_hours ≤ 50 - All times are in October 2022
-
out_timecan be one day afterin_time
Visualization
Tap to expand
Understanding the Visualization
1
Input Tables
Employee requirements and work logs
2
LEFT JOIN
Combine employees with their work sessions
3
Filter
Find employees with insufficient hours
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to ensure all employees are evaluated, even those without logged work sessions
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code