Top Operators per Shift by Good Output — SQL Problem
Operators log the good units they produced on each machine during a shift (A, B or C); an operator who ran two machines in one shift has two log rows.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #258
Problem statement
Operators log the good units they produced on each machine during a shift (A, B or C); an operator who ran two machines in one shift has two log rows.
For every shift — a shift_date and shift pair — find the operator or operators with the highest total good units in that shift (sum over their logs). If several operators tie for the top, list all of them. Return shift_date, shift, operator_name and units, ordered by shift_date, then shift, then operator_name.
Tables
Table: ShiftOutput
| Column | Type |
|---|---|
| log_id | int |
| shift_date | date |
| shift | enum(A, B, C) |
| operator_name | varchar |
| machine_code | varchar |
| units_ok | int |
Primary key: log_id.
Operator names are unique.
Examples
Example 1
ShiftOutput
| log_id | shift_date | shift | operator_name | machine_code | units_ok |
|---|---|---|---|---|---|
| 1 | 2025-03-03 | A | Ravi | CNC-07 | 140 |
| 2 | 2025-03-03 | A | Ravi | CNC-08 | 60 |
| 3 | 2025-03-03 | A | Meera | CNC-09 | 185 |
| 4 | 2025-03-03 | B | Kabir | CNC-07 | 150 |
| 5 | 2025-03-03 | B | Zara | CNC-08 | 150 |
| 6 | 2025-03-04 | A | Meera | CNC-07 | 170 |
| 7 | 2025-03-04 | A | Ravi | CNC-08 | 90 |
Output
| shift_date | shift | operator_name | units |
|---|---|---|---|
| 2025-03-03 | A | Ravi | 200 |
| 2025-03-03 | B | Kabir | 150 |
| 2025-03-03 | B | Zara | 150 |
| 2025-03-04 | A | Meera | 170 |
How to solve Top Operators per Shift by Good Output
Two steps, in that order. First aggregate: an operator's output in a shift is the sum of their log rows, so group by shift_date, shift and operator_name. Ranking the raw logs instead would compare one machine's output with another operator's whole shift.
Then rank within each shift: RANK() OVER (PARTITION BY shift_date, shift ORDER BY units DESC) numbers the operators of a shift from the top, giving equal totals the same rank. Keeping rnk = 1 returns every operator tied for first — ROW_NUMBER would arbitrarily keep only one, and which one could change with storage order.
Without a ranking function, compare each total with the shift's maximum through a correlated subquery over the same CTE, or compute MAX(SUM(units_ok)) OVER (PARTITION BY …) in the grouped query itself — a window over an aggregate is evaluated after GROUP BY. All three are one aggregation plus a pass over the much smaller set of totals.
Reference solution (MySQL)
WITH totals AS (
SELECT shift_date, shift, operator_name, SUM(units_ok) AS units
FROM ShiftOutput
GROUP BY shift_date, shift, operator_name
), ranked AS (
SELECT t.*, RANK() OVER (PARTITION BY shift_date, shift ORDER BY units DESC) AS rnk
FROM totals t
)
SELECT shift_date, shift, operator_name, units
FROM ranked
WHERE rnk = 1
ORDER BY shift_date, shift, operator_nameAnother way
WITH totals AS (
SELECT shift_date, shift, operator_name, SUM(units_ok) AS units FROM ShiftOutput GROUP BY shift_date, shift, operator_name
)
SELECT t.shift_date, t.shift, t.operator_name, t.units FROM totals t
WHERE t.units = (SELECT MAX(u.units) FROM totals u WHERE u.shift_date = t.shift_date AND u.shift = t.shift)
ORDER BY t.shift_date, t.shift, t.operator_nameAnother way
SELECT shift_date, shift, operator_name, units FROM (
SELECT shift_date, shift, operator_name, SUM(units_ok) AS units,
MAX(SUM(units_ok)) OVER (PARTITION BY shift_date, shift) AS best
FROM ShiftOutput GROUP BY shift_date, shift, operator_name
) x WHERE units = best ORDER BY shift_date, shift, operator_name← Sensor Threshold Breach Rate · Late Work Order Rate by Line →