Batches Worse Than Their Product's Defect Rate

Medium
261.

Batches Worse Than Their Product's Defect Rate

Quality engineers compare every batch with its product's overall record. A product's pooled defect rate is the total defective units of all its batches divided by the total units produced by all its batches.

Return the batches whose own defect rate (defective units ÷ units produced) is strictly higher than their product's pooled rate, with the columns batch_id, product_code, units_produced and defective_units. Batches that produced 0 units are never in the answer, and they add nothing to the pooled rate. Order by product_code, then batch_id.

Tables

Table: Batch

ColumnType
batch_idint
product_codevarchar
units_producedint
defective_unitsint

Primary key: batch_id.

A cancelled batch has units_produced = 0 and defective_units = 0.

Example

The rows must come back in the order shown.

Batch

batch_idproduct_codeunits_produceddefective_units
601GEAR-12T100020
602GEAR-12T50015
603GEAR-12T5005
604VALVE-HP8008
605VALVE-HP4004
606AXLE-S00
607AXLE-S120030

Output

batch_idproduct_codeunits_produceddefective_units
602GEAR-12T50015
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.

Batches Worse Than Their Product's Defect Rate — SQL Problem

Quality engineers compare every batch with its product's overall record.

  • Difficulty: Medium
  • Topics: Subqueries, Aggregation
  • Dialect: MySQL
  • Problem: #261

Problem statement

Quality engineers compare every batch with its product's overall record. A product's pooled defect rate is the total defective units of all its batches divided by the total units produced by all its batches.

Return the batches whose own defect rate (defective units ÷ units produced) is strictly higher than their product's pooled rate, with the columns batch_id, product_code, units_produced and defective_units. Batches that produced 0 units are never in the answer, and they add nothing to the pooled rate. Order by product_code, then batch_id.

Tables

Table: Batch

ColumnType
batch_idint
product_codevarchar
units_producedint
defective_unitsint

Primary key: batch_id.

A cancelled batch has units_produced = 0 and defective_units = 0.

Examples

Example 1

Batch

batch_idproduct_codeunits_produceddefective_units
601GEAR-12T100020
602GEAR-12T50015
603GEAR-12T5005
604VALVE-HP8008
605VALVE-HP4004
606AXLE-S00
607AXLE-S120030

Output

batch_idproduct_codeunits_produceddefective_units
602GEAR-12T50015

How to solve Batches Worse Than Their Product's Defect Rate

The benchmark is a per-product aggregate, and each batch row is compared with its own product's value — the shape of a correlated comparison. Computing the totals once in a derived table (SUM(units_produced), SUM(defective_units) per product) and joining it back is the usual form; a correlated scalar subquery per batch, or window sums partitioned by product, give the same numbers.

Note that the pooled rate is a ratio of sums, not the average of batch rates: a big batch counts for more. Averaging rates would weigh a 200-unit trial batch like a 2,000-unit run.

Instead of dividing, compare by cross-multiplying: defective_units / units_produced > bad / units is defective_units * units > bad * units_produced for positive totals. Integer arithmetic makes a batch exactly at the pooled rate compare equal — it is not "strictly higher" — and needs no rounding. Cancelled batches contribute zeros to both sums, so they change nothing, and units_produced > 0 keeps them out of the answer.

Reference solution (MySQL)

SELECT b.batch_id, b.product_code, b.units_produced, b.defective_units
FROM Batch b
JOIN (
  SELECT product_code, SUM(units_produced) AS units, SUM(defective_units) AS bad
  FROM Batch
  GROUP BY product_code
) p ON p.product_code = b.product_code
WHERE b.units_produced > 0
  AND b.defective_units * p.units > p.bad * b.units_produced
ORDER BY b.product_code, b.batch_id

Another way

SELECT batch_id, product_code, units_produced, defective_units FROM Batch b
WHERE units_produced > 0
  AND defective_units * (SELECT SUM(units_produced) FROM Batch x WHERE x.product_code = b.product_code)
    > units_produced * (SELECT SUM(defective_units) FROM Batch x WHERE x.product_code = b.product_code)
ORDER BY product_code, batch_id

Another way

SELECT batch_id, product_code, units_produced, defective_units FROM (
  SELECT b.*, SUM(units_produced) OVER (PARTITION BY product_code) AS pu,
         SUM(defective_units) OVER (PARTITION BY product_code) AS pd
  FROM Batch b
) t WHERE units_produced > 0 AND defective_units * pu > pd * units_produced
ORDER BY product_code, batch_id

← Machines Due for Preventive Maintenance by Quarter End · Solar Generation Sharp Day-Over-Day Drops →