Amazon: Associates Meeting the 100 Units-per-Hour Pick Target

Easy
576.

Amazon: Associates Meeting the 100 Units-per-Hour Pick Target

EasyAggregation

MySQL

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

ColumnType
shift_idint
associate_idint
shift_datedate
units_pickedint
hours_workeddecimal

Primary key: shift_id.

One row per shift an associate spent picking; hours_worked is in half-hour steps.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PickShift

shift_idassociate_idshift_dateunits_pickedhours_worked
1112025-06-028208
2112025-06-037607.5
3112025-06-049208
4122025-06-02100010
5122025-06-036006
6122025-06-054004
7132025-06-02150010
8132025-06-03140010
9142025-06-027008
10142025-06-039508
1 more row

Output

associate_idshiftstotal_unitstotal_hours
113250023.5
123200020
143255024
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
shift_idint
associate_idint
shift_datedate
units_pickedint
hours_workeddecimal

Primary key: shift_id.

One row per shift an associate spent picking; hours_worked is in half-hour steps.

Examples

Example 1

PickShift

shift_idassociate_idshift_dateunits_pickedhours_worked
1112025-06-028208
2112025-06-037607.5
3112025-06-049208
4122025-06-02100010
5122025-06-036006
6122025-06-054004
7132025-06-02150010
8132025-06-03140010
9142025-06-027008
10142025-06-039508
11142025-06-049008

Output

associate_idshiftstotal_unitstotal_hours
113250023.5
123200020
143255024

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 →