Compute daily work hours from in/out events
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Given punch events, compute each employee’s daily hours, handling unmatched events and overnight shifts. Write SQL over:
events(employee_id INT, evt_ts TIMESTAMP, action VARCHAR CHECK(action IN ('in','out')))
Sample rows:
101 | 2025-01-30 08:00 | in
101 | 2025-01-30 13:00 | out
101 | 2025-01-30 14:00 | in
101 | 2025-01-30 18:30 | out
102 | 2025-01-30 22:00 | in
102 | 2025-01-31 06:00 | out
103 | 2025-01-30 09:00 | in
103 | 2025-01-30 12:00 | out
103 | 2025-01-30 12:30 | out -- duplicate/misordered
Requirements: (a) pair each 'in' with the next 'out' for the same employee; (b) split work that crosses midnight into the appropriate calendar days; (c) ignore orphan 'out' events and cap trailing unmatched 'in' at 23:59:59 of that day; (d) sum hours per employee_id per work_date with rounding to nearest 0.25 hr; (e) flag days with data-quality issues (overlaps, consecutive ins/outs). Return columns: employee_id, work_date, hours_worked, dq_issue_flag.
Overview: This question evaluates a candidate's ability to manipulate time-series punch-event data, including temporal pairing of in/out events, splitting shifts across calendar days, rounding and aggregating hours, and detecting data-quality issues such as overlaps or orphaned events using SQL or Python.
Read the full Amazon Data Scientist interview experience this question came from
You are given a table of punch events where each row represents an employee clocking in or out.
Table:
events(employee_id INT, evt_ts TIMESTAMP, action VARCHAR CHECK(action IN ('in','out')))
Using this table, write a SQL query that produces daily work hours per employee with the following requirements:
1) For each employee, pair each 'in' event with the next 'out' event in chronological order (orphan 'out' events that do not have a corresponding 'in' must be ignored).
2) If a work interval crosses midnight, split the time into separate calendar days so that hours are attributed to the correct work_date.
3) If an 'in' event has no subsequent 'out', cap the interval at 23:59:59 of that same calendar day.
4) Sum the total hours per employee_id per work_date and round the result to the nearest 0.25 hour (15 minutes).
5) Flag days that have data-quality issues:
- consecutive events with the same action for the same employee (e.g., 'in' followed by 'in', or 'out' followed by 'out');
- overlapping work intervals for the same employee.
Return one row per employee per work_date with the following columns:
- employee_id
- work_date (DATE)
- hours_worked (total rounded hours for that day)
- dq_issue_flag (CHAR(1), 'Y' if any data-quality issue on that day for that employee, otherwise 'N').
Tables
events(employee_id INT, evt_ts TIMESTAMP, action VARCHAR(3))
Hints
- First convert raw events into in/out intervals using window functions or row-numbering, then handle unmatched ins by capping at the end of the day.
- After building intervals, use generate_series over dates to split overnight shifts by day, aggregate hours, and separately detect consecutive same-action events and overlapping intervals for the data-quality flag.