Coding questions · Amazon
Compute daily work hours from in/out events
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.
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.
Compute daily work hours from in/out events with overnight shifts and data-quality flags
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:
- 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).
- If a work interval crosses midnight, split the time into separate calendar days so that hours are attributed to the correct work_date.
- If an 'in' event has no subsequent 'out', cap the interval at 23:59:59 of that same calendar day.
- Sum the total hours per employee_id per work_date and round the result to the nearest 0.25 hour (15 minutes).
- 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').
Table events
| column | type | constraints |
|---|---|---|
| employee_id | INT | NOT NULL |
| evt_ts | TIMESTAMP | NOT NULL |
| action | VARCHAR(3) | NOT NULL, CHECK (action IN ('in','out')) |
Sample data:
| action | employee_id | evt_ts |
|---|---|---|
| in | 101 | 2025-01-30 08:00:00 |
| out | 101 | 2025-01-30 13:00:00 |
| in | 101 | 2025-01-30 14:00:00 |
| out | 101 | 2025-01-30 18:30:00 |
| in | 102 | 2025-01-30 22:00:00 |
| out | 102 | 2025-01-31 06:00:00 |
| in | 103 | 2025-01-30 09:00:00 |
| out | 103 | 2025-01-30 12:00:00 |
| out | 103 | 2025-01-30 12:30:00 |
| in | 104 | 2025-01-30 20:00:00 |
| in | 105 | 2025-01-30 08:00:00 |
| in | 105 | 2025-01-30 10:00:00 |
| out | 105 | 2025-01-30 12:00:00 |
| out | 105 | 2025-01-30 13:00:00 |
Expected output
For the sample data, the query should compute valid in/out intervals, split any overnight work across calendar days, cap unmatched 'in' events at 23:59:59, sum and round hours per employee per day, and flag days with consecutive same-action events or overlapping intervals.
Interpretation of sample data:
- Emp 101 on 2025-01-30 works 08:00–13:00 (5h) and 14:00–18:30 (4.5h) = 9.5h.
- Emp 102 works 2025-01-30 22:00 to 2025-01-31 06:00: 2h on 2025-01-30 and 6h on 2025-01-31.
- Emp 103 has 09:00–12:00 (3h); the extra 12:30 'out' is an orphan and ignored for hours, but causes a data-quality flag.
- Emp 104 has an unmatched 'in' at 20:00 on 2025-01-30, capped at 23:59:59 for ~4.0h after rounding.
- Emp 105 has intervals 08:00–12:00 (4h) and 10:00–13:00 (3h), which overlap and also include consecutive 'in' and 'out' events; the day is flagged, and total hours sum to 7.0h (overlapping time is not de-duplicated).
| dq_issue_flag | employee_id | hours_worked | work_date |
|---|---|---|---|
| N | 101 | 9.5 | 2025-01-30 |
| N | 102 | 2 | 2025-01-30 |
| N | 102 | 6 | 2025-01-31 |
| Y | 103 | 3 | 2025-01-30 |
| N | 104 | 4 | 2025-01-30 |
| Y | 105 | 7 | 2025-01-30 |
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.