Find Total Time Spent by Each Employee - Problem
Given a table Employees that tracks employee check-in and check-out times, calculate the total time in minutes spent by each employee on each day at the office.
Key Points:
- An employee can have multiple entries on the same day
- Time spent for each entry =
out_time - in_time - Times are in minutes (1-1440, representing minutes in a day)
- Sum all time periods for each employee on each day
Table Schema
Employees
| Column Name | Type | Description |
|---|---|---|
emp_id
PK
|
int | Employee ID |
event_day
PK
|
date | Date when the entry/exit occurred |
in_time
PK
|
int | Check-in time in minutes (1-1440) |
out_time
|
int | Check-out time in minutes (1-1440) |
Primary Key: (emp_id, event_day, in_time)
Note: Each row represents one entry-exit period. An employee can have multiple records per day.
Input & Output
Example 1 — Multiple Entries Same Day
Input Table:
| emp_id | event_day | in_time | out_time |
|---|---|---|---|
| 1 | 2020-11-28 | 4 | 32 |
| 1 | 2020-11-28 | 55 | 200 |
| 2 | 2020-11-29 | 3 | 33 |
Output:
| emp_id | event_day | total_time |
|---|---|---|
| 1 | 2020-11-28 | 173 |
| 2 | 2020-11-29 | 30 |
💡 Note:
Employee 1 has two entries on 2020-11-28: (32-4) + (200-55) = 28 + 145 = 173 minutes total. Employee 2 has one entry on 2020-11-29: (33-3) = 30 minutes total.
Example 2 — Single Entry Per Day
Input Table:
| emp_id | event_day | in_time | out_time |
|---|---|---|---|
| 3 | 2020-12-01 | 480 | 960 |
| 4 | 2020-12-01 | 500 | 520 |
Output:
| emp_id | event_day | total_time |
|---|---|---|
| 3 | 2020-12-01 | 480 |
| 4 | 2020-12-01 | 20 |
💡 Note:
Employee 3 worked 960-480 = 480 minutes (8 hours). Employee 4 worked 520-500 = 20 minutes. Each has only one entry, so no aggregation needed.
Constraints
-
1 ≤ emp_id ≤ 100 -
1 ≤ in_time < out_time ≤ 1440 -
event_dayis a valid date - No two events on the same day intersect in time
Visualization
Tap to expand
Understanding the Visualization
1
Input Data
Employee check-in/out records
2
GROUP BY
Group by employee and day
3
SUM
Aggregate time differences
Key Takeaway
🎯 Key Insight: Use GROUP BY to aggregate multiple time periods per employee per day
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code