Loading problem...
A workplace analytics system stores employee attendance intervals in a single table.
Table: employees
Data guarantees:
Task: For every employee and day combination that appears in the table, compute total time spent in office in minutes.
Formula:
Output requirements:
Supported submission environments:
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}
]Explanation
Employee 1 has two intervals on 2024-02-01, so both durations are summed. The same logic applies independently per (day, employee).
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 |[
{"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.
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 |[
{"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