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
| Column | Type |
|---|---|
| batch_id | int |
| product_code | varchar |
| units_produced | int |
| defective_units | int |
Primary key: batch_id.
A cancelled batch has units_produced = 0 and defective_units = 0.
Examples
Example 1
Batch
| batch_id | product_code | units_produced | defective_units |
|---|---|---|---|
| 601 | GEAR-12T | 1000 | 20 |
| 602 | GEAR-12T | 500 | 15 |
| 603 | GEAR-12T | 500 | 5 |
| 604 | VALVE-HP | 800 | 8 |
| 605 | VALVE-HP | 400 | 4 |
| 606 | AXLE-S | 0 | 0 |
| 607 | AXLE-S | 1200 | 30 |
Output
| batch_id | product_code | units_produced | defective_units |
|---|---|---|---|
| 602 | GEAR-12T | 500 | 15 |
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_idAnother 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_idAnother 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 →