Top Operators per Shift by Good Output

Medium
258.

Top Operators per Shift by Good Output

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

ColumnTypeValues
log_idint
shift_datedate
shiftenumA, B, C
operator_namevarchar
machine_codevarchar
units_okint

Primary key: log_id.

Operator names are unique.

Example

The rows must come back in the order shown.

ShiftOutput

log_idshift_dateshiftoperator_namemachine_codeunits_ok
12025-03-03ARaviCNC-07140
22025-03-03ARaviCNC-0860
32025-03-03AMeeraCNC-09185
42025-03-03BKabirCNC-07150
52025-03-03BZaraCNC-08150
62025-03-04AMeeraCNC-07170
72025-03-04ARaviCNC-0890

Output

shift_dateshiftoperator_nameunits
2025-03-03ARavi200
2025-03-03BKabir150
2025-03-03BZara150
2025-03-04AMeera170
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.

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

ColumnType
log_idint
shift_datedate
shiftenum(A, B, C)
operator_namevarchar
machine_codevarchar
units_okint

Primary key: log_id.

Operator names are unique.

Examples

Example 1

ShiftOutput

log_idshift_dateshiftoperator_namemachine_codeunits_ok
12025-03-03ARaviCNC-07140
22025-03-03ARaviCNC-0860
32025-03-03AMeeraCNC-09185
42025-03-03BKabirCNC-07150
52025-03-03BZaraCNC-08150
62025-03-04AMeeraCNC-07170
72025-03-04ARaviCNC-0890

Output

shift_dateshiftoperator_nameunits
2025-03-03ARavi200
2025-03-03BKabir150
2025-03-03BZara150
2025-03-04AMeera170

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_name

Another 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_name

Another 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 →