Find Total Time Spent
ASKED IN INTERVIEWSlack
Problem Statement
Slack office management tracks employee attendance through an automated badge system. Each time an employee enters or leaves the workspace, a log entry is created recording the event day, the employee ID, and the specific hour of entry or exit.
Your task is to calculate the total time (in minutes or hours as per the integers) spent by each employee in the office for each unique day.
Rules:
- The total time spent is calculated by subtracting the entry time from the exit time for each record.
- Output columns: day, emp_id, total_time.
- Results must be sorted by day in ascending order, and then by emp_id in ascending order.
Tests your understanding of
Arithmetic, Aggregation and Grouping
Input Tables
employees_log
| 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 |
office_locations
| office_id(INTEGER) | office_name(VARCHAR) |
|---|---|
| 1 | San Francisco |
| 2 | Vancouver |
| 3 | Dublin |
emp_departments
| emp_id(INTEGER) | dept_name(VARCHAR) |
|---|---|
| 1 | Engineering |
| 2 | Product |
| 3 | Design |
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
EasyASKED IN INTERVIEWArithmeticAggregationGrouping
10-15 min
92%