Find Total Time Spent By Each Employee Problem

1
2
3
4
5
6
7
8
9
10
11
12
+-------------+------+
| Column Name | Type |
+-------------+------+
| emp_id      | int  |
| event_day   | date |
| in_time     | int  |
| out_time    | int  |
+-------------+------+
(emp_id, event_day, in_time) is the primary key of this table.
The table shows the employees' entries and exits in an office.
event_day is the day at which this event happened and in_time is the minute at which the employee entered the office and out_time is the time at which he got outnumbered from 1 to 1440.
It's guaranteed that no two events on the same day intersect in time.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Employees table:
+--------+------------+---------+----------+
| emp_id | event_day  | in_time | out_time |
+--------+------------+---------+----------+
| 1      | 2020-11-28 | 4       | 32       |
| 1      | 2020-11-28 | 55      | 200      |
| 1      | 2020-12-03 | 1       | 42       |
| 2      | 2020-11-28 | 3       | 33       |
| 2      | 2020-12-09 | 47      | 74       |
+--------+------------+---------+----------+
Result table:
+------------+--------+------------+
| day        | emp_id | total_time |
+------------+--------+------------+
| 2020-11-28 | 1      | 173        |
| 2020-11-28 | 2      | 30         |
| 2020-12-03 | 1      | 41         |
| 2020-12-09 | 2      | 27         |
+------------+--------+------------+
Employee 1 has three events two on day 2020-11-28 with a total of (32 - 4) + (200-55) = 173 and one on day 2020-12-03 with a total of (42 - 1) = 41.
Employee 2 has two events one on day 2020-11-28 with a total of (33-3) = 30 and one on day 2020-12-09 with a total of (74 - 47) = 27.

1
2
3
select event_day as day, emp_id, sum(out_time - in_time) as total_time
from Employees
group by day, emp_id