8000 Initial Commit · priyesh0453/SQL-LeetCode@026f5d5 · GitHub
[go: up one dir, main page]
Skip to content

Commit 026f5d5

Browse files
priyesh0453priyesh0453
committed
Initial Commit
1 parent b75f2f0 commit 026f5d5

File tree

1 file changed

+62
-0
lines changed

1 file changed

+62
-0
lines changed

‎Easy/1741. Find Total Time Spent by Each Employee.sql

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
/*
2+
Table: Employees
3+
4+
+-------------+------+
5+
| Column Name | Type |
6+
+-------------+------+
7+
| emp_id | int |
8+
| event_day | date |
9+
| in_time | int |
10+
| out_time | int |
11+
+-------------+------+
12+
(emp_id, event_day, in_time) is the primary key of this table.
13+
The table shows the employees' entries and exits in an office.
14+
event_day is the day at which this event happened, in_time is the minute at which the employee entered the office, and out_time is the minute at which they left the office.
15+
in_time and out_time are between 1 and 1440.
16+
It is guaranteed that no two events on the same day intersect in time, and in_time < out_time.
17+
18+
19+
Required Query:
20+
21+
1) 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.
22+
23+
24+
SQL Schema:
25+
26+
Create table If Not Exists Employees(emp_id int, event_day date, in_time int, out_time int)
27+
Truncate table Employees
28+
insert into Employees (emp_id, event_day, in_time, out_time) values ('1', '2020-11-28', '4', '32')
29+
insert into Employees (emp_id, event_day, in_time, out_time) values ('1', '2020-11-28', '55', '200')
30+
insert into Employees (emp_id, event_day, in_time, out_time) values ('1', '2020-12-3', '1', '42')
31+
insert into Employees (emp_id, event_day, in_time, out_time) values ('2', '2020-11-28', '3', '33')
32+
insert into Employees (emp_id, event_day, in_time, out_time) values ('2', '2020-12-9', '47', '74')
33+
34+
35+
Example:
36+
37+
Input:
38+
Employees table:
39+
+--------+------------+---------+----------+
40+
| emp_id | event_day | in_time | out_time |
41+
+--------+------------+---------+----------+
42+
| 1 | 2020-11-28 | 4 | 32 |
43+
| 1 | 2020-11-28 | 55 | 200 |
44+
| 1 | 2020-12-03 | 1 | 42 |
45+
| 2 | 2020-11-28 | 3 | 33 |
46+
| 2 | 2020-12-09 | 47 | 74 |
47+
+--------+------------+---------+----------+
48+
Output:
49+
+------------+--------+------------+
50+
| day | emp_id | total_time |
51+
+------------+--------+------------+
52+
| 2020-11-28 | 1 | 173 |
53+
| 2020-11-28 | 2 | 30 |
54+
| 2020-12-03 | 1 | 41 |
55+
| 2020-12-09 | 2 | 27 |
56+
+------------+--------+------------+
57+
*/
58+
59+
--Solution:
60+
SELECT event_day AS 'day', emp_id, SUM(out_time - in_time) AS 'total_time'
61+
FROM Employees
62+
GROUP BY emp_id, event_day

0 commit comments

Comments
 (0)
0