Daily Office Minutes by Employee

Easy

A workplace analytics system stores employee attendance intervals in a single table. Table: employees emp_id: unique employee identifier event_day: calendar date of the attendance interval in_time: minute in the day when the employee entered (1 to 1440) out_time: minute in the day when the employee exited (1 to 1440) Data guarantees: (emp_id, event_day, in_time) is unique. For each row, in_time < out_time. For the same employee on the same day, intervals do not overlap. Task: For every employee and day combination that appears in the table, compute total time spent in office in minutes. Formula: interval_minutes = out_time - in_time total_time = sum(interval_minutes) grouped by (event_day, emp_id) Output requirements: Return exactly these columns: day emp_id total_time day should be the event_day value. Rows may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

employees:
| emp_id | event_day  | in_time | out_time |
|--------|------------|---------|----------|
| 1      | 2024-02-01 | 4       | 32       |
| 1      | 2024-02-01 | 55      | 200      |
| 1      | 2024-02-03 | 1       | 42       |
| 2      | 2024-02-01 | 3       | 33       |
| 2      | 2024-02-09 | 47      | 74       |

Output

[
  {"day":"2024-02-01","emp_id":1,"total_time":173},
  {"day":"2024-02-01","emp_id":2,"total_time":30},
  {"day":"2024-02-03","emp_id":1,"total_time":41},
  {"day":"2024-02-09","emp_id":2,"total_time":27}
]

Explanation

Employee 1 has two intervals on 2024-02-01, so both durations are summed. The same logic applies independently per (day, employee).

Example 2

Input

employees:
| emp_id | event_day  | in_time | out_time |
|--------|------------|---------|----------|
| 7      | 2024-04-10 | 60      | 180      |
| 7      | 2024-04-10 | 300     | 360      |
| 7      | 2024-04-11 | 100     | 220      |

Output

[
  {"day":"2024-04-10","emp_id":7,"total_time":180},
  {"day":"2024-04-11","emp_id":7,"total_time":120}
]

Explanation

The same employee appears on multiple days. Aggregation is day-specific, so totals are computed separately for each date.

Example 3

Input

employees:
| emp_id | event_day  | in_time | out_time |
|--------|------------|---------|----------|
| 10     | 2024-01-05 | 5       | 20       |
| 11     | 2024-01-05 | 15      | 25       |
| 10     | 2024-01-05 | 40      | 60       |
| 11     | 2024-01-06 | 1       | 10       |

Output

[
  {"day":"2024-01-05","emp_id":10,"total_time":35},
  {"day":"2024-01-05","emp_id":11,"total_time":10},
  {"day":"2024-01-06","emp_id":11,"total_time":9}
]

Explanation

Multiple employees can share the same day. Grouping by both day and emp_id prevents cross-employee mixing.

Constraints

1 <= number of rows in employees <= 200000
1 <= emp_id <= 1000000000
event_day is a valid date
1 <= in_time < out_time <= 1440
(emp_id, event_day, in_time) is unique
Intervals for the same (emp_id, event_day) do not overlap
Return exactly columns: day, emp_id, total_time
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: day, emp_id, total_time

employees: | emp_id | event_day | in_time | out_time | |--------|------------|---------|----------| | 1 | 2024-02-01 | 4 | 32 | | 1 | 2024-02-01 | 55 | 200 | | 1 | 2024-02-03 | 1 | 42 | | 2 | 2024-02-01 | 3 | 33 | | 2 | 2024-02-09 | 47 | 74 |

[ {"day":"2024-02-01","emp_id":1,"total_time":173}, {"day":"2024-02-01","emp_id":2,"total_time":30}, {"day":"2024-02-03","emp_id":1,"total_time":41}, {"day":"2024-02-09","emp_id":2,"total_time":27} ]

employees: | emp_id | event_day | in_time | out_time | |--------|------------|---------|----------| | 7 | 2024-04-10 | 60 | 180 | | 7 | 2024-04-10 | 300 | 360 | | 7 | 2024-04-11 | 100 | 220 |

employees: | emp_id | event_day | in_time | out_time | |--------|------------|---------|----------| | 10 | 2024-01-05 | 5 | 20 | | 11 | 2024-01-05 | 15 | 25 | | 10 | 2024-01-05 | 40 | 60 | | 11 | 2024-01-06 | 1 | 10 |