Daily Office Minutes by Employee

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employees =

[{"emp_id":1,"event_day":"2024-02-01","in_time":4,"out_time":32},{"emp_id":2,"event_day":"2024-02-01","in_time":3,"out_time":33},{"emp_id":1,"event_day":"2024-02-03","in_time":1,"out_time":42},{"emp_id":2,"event_day":"2024-02-09","in_time":47,"out_time":74},{"emp_id":1,"event_day":"2024-02-01","in_time":55,"out_time":200}]

Daily Office Minutes by Employee

Hints

Daily Office Minutes by Employee

Hints