Amazon: Associates Meeting the 100 Units-per-Hour Pick Target
A fulfilment centre's area manager reviews pickers' productivity across the week.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #576
Problem statement
A fulfilment centre's area manager reviews pickers' productivity across the week. For this exercise the target is 100 units per hour, measured over all of an associate's shifts together, not shift by shift.
Return every associate who worked at least 3 shifts and whose total units picked is at least 100 times their total hours worked. Return associate_id, shifts, total_units and total_hours, in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: PickShift
| Column | Type |
|---|---|
| shift_id | int |
| associate_id | int |
| shift_date | date |
| units_picked | int |
| hours_worked | decimal |
Primary key: shift_id.
One row per shift an associate spent picking; hours_worked is in half-hour steps.
Examples
Example 1
PickShift
| shift_id | associate_id | shift_date | units_picked | hours_worked |
|---|---|---|---|---|
| 1 | 11 | 2025-06-02 | 820 | 8 |
| 2 | 11 | 2025-06-03 | 760 | 7.5 |
| 3 | 11 | 2025-06-04 | 920 | 8 |
| 4 | 12 | 2025-06-02 | 1000 | 10 |
| 5 | 12 | 2025-06-03 | 600 | 6 |
| 6 | 12 | 2025-06-05 | 400 | 4 |
| 7 | 13 | 2025-06-02 | 1500 | 10 |
| 8 | 13 | 2025-06-03 | 1400 | 10 |
| 9 | 14 | 2025-06-02 | 700 | 8 |
| 10 | 14 | 2025-06-03 | 950 | 8 |
| 11 | 14 | 2025-06-04 | 900 | 8 |
Output
| associate_id | shifts | total_units | total_hours |
|---|---|---|---|
| 11 | 3 | 2500 | 23.5 |
| 12 | 3 | 2000 | 20 |
| 14 | 3 | 2550 | 24 |
How to solve Amazon: Associates Meeting the 100 Units-per-Hour Pick Target
The rate is defined over an associate's whole week, so the query groups by associate_id and computes the three totals: COUNT(*) shifts, SUM(units_picked) and SUM(hours_worked). Averaging each shift's own rate would be a different (and wrong) number, because a short shift would weigh as much as a ten-hour one.
Both conditions are about the groups, so they go in HAVING: at least three shifts, and units at least 100 times hours. Writing the test as a multiplication avoids division altogether; the alternative divides in a derived table instead, which gives the same answer because hours are always positive. The boundary is inclusive — an associate at exactly 100 an hour meets the target.
Associates with fewer than three shifts are left out however fast they were, since a single strong shift says little. The query is one grouped scan of the shift table.
Reference solution (MySQL)
SELECT associate_id, COUNT(*) AS shifts, SUM(units_picked) AS total_units, SUM(hours_worked) AS total_hours
FROM PickShift
GROUP BY associate_id
HAVING COUNT(*) >= 3 AND SUM(units_picked) >= 100 * SUM(hours_worked)Another way
SELECT associate_id, shifts, total_units, total_hours FROM (SELECT associate_id, COUNT(shift_id) AS shifts, SUM(units_picked) AS total_units, SUM(hours_worked) AS total_hours FROM PickShift GROUP BY associate_id) t WHERE shifts >= 3 AND total_units / total_hours >= 100← Amazon: Delivery Routes Loaded Beyond Their Van's Capacity · Amazon: Return Requests With No Delivered Order Behind Them →