Total Time Spent by Each Employee
Slack
Problem Statement
Slack HR wants to track office utilization. Employees may enter and leave the office multiple times in a single day. You need to calculate the total time each employee spends in the office for each day they were present.
Write an SQL query to calculate the total time in minutes spent by each employee on each day at the office.
Rules:
- The total time spent on a day is the sum of (out_time - in_time) for all entries of that employee on that day.
- Return the result table with columns day, emp_id, and total_time.
- Results must be sorted by day and emp_id in ascending order.
Tests your understanding of
Basic SQL, Aggregation, Math and Grouping
Input Tables
employees
| emp_id(INTEGER) | event_day(DATE) | in_time(INTEGER) | out_time(INTEGER) |
|---|---|---|---|
| 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 |
Expected Output
| day(DATE) | emp_id(INTEGER) | total_time(INTEGER) |
|---|---|---|
| 2020-11-28 | 1 | 173 |
| 2020-11-28 | 2 | 30 |
| 2020-12-03 | 1 | 41 |
| 2020-12-09 | 2 | 27 |
Tags
EasyBasic SQLAggregationMathGrouping
5-10 min
90%