Skip to content

Q1741TotalTime.md

Latest commit

 

History

History
35 lines (27 loc) · 1.39 KB

Q1741TotalTime.md

File metadata and controls

35 lines (27 loc) · 1.39 KB

1741. Find Total Time Spent by Each Employee

Solution

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

Question Description

Write an SQL query to calculate the total time in minutes spent by each employee on each day at the office. Note that within one day, an employee can enter and leave more than once. The time spent in the office for a single entry is out_time - in_time.

Return the result table in any order.

The query result format is in the following example.

Input: 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

Output:

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

Explanation: 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.