Production Batches Over Two Percent Defects — SQL Problem
Every production batch records how many units were made and how many of them failed final inspection.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #251
Problem statement
Every production batch records how many units were made and how many of them failed final inspection. The plant's quality target is a defect rate of at most 2%.
Return the batches whose defect rate is strictly greater than 2%, with the columns batch_id, product_code and defect_rate_pct — the defective units as a percentage of the units produced, rounded to two decimals. A batch that produced 0 units (a cancelled run) is never in the answer. Order the rows by defect_rate_pct from highest to lowest, then by batch_id.
Tables
Table: ProductionBatch
| Column | Type |
|---|---|
| batch_id | int |
| product_code | varchar |
| units_produced | int |
| defective_units | int |
Primary key: batch_id.
One row per batch; defective_units is never more than units_produced.
Examples
Example 1
ProductionBatch
| batch_id | product_code | units_produced | defective_units |
|---|---|---|---|
| 501 | BR-DISC | 1200 | 18 |
| 502 | BR-DISC | 1500 | 30 |
| 503 | GEAR-12T | 800 | 31 |
| 504 | AXLE-S | 0 | 0 |
| 505 | GEAR-12T | 750 | 24 |
| 506 | PISTON-80 | 2000 | 41 |
Output
| batch_id | product_code | defect_rate_pct |
|---|---|---|
| 503 | GEAR-12T | 3.88 |
| 505 | GEAR-12T | 3.2 |
| 506 | PISTON-80 | 2.05 |
How to solve Production Batches Over Two Percent Defects
The rate is 100 * defective_units / units_produced; the question keeps the batches where it exceeds 2 and prints it rounded to two decimals. The cleanest filter avoids division altogether: multiplying both sides by the (positive) units gives defective_units * 100 > units_produced * 2, an exact integer comparison, so a batch at exactly 2% — 30 bad out of 1,500 — is reliably left out.
A batch with zero units has no rate at all. MySQL returns NULL for a division by zero, and the integer test 0 > 0 is false anyway, but stating units_produced > 0 makes the intent visible and protects the division in the SELECT list; NULLIF(units_produced, 0) in the alternative does the same job.
The order is by the rounded rate, highest first, with batch_id breaking ties so equal rates always come out the same way. One scan of the table, then a sort of the few batches that failed.
Reference solution (MySQL)
SELECT batch_id, product_code,
ROUND(100 * defective_units / units_produced, 2) AS defect_rate_pct
FROM ProductionBatch
WHERE units_produced > 0
AND defective_units * 100 > units_produced * 2
ORDER BY defect_rate_pct DESC, batch_idAnother way
SELECT batch_id, product_code, ROUND(defective_units * 100 / NULLIF(units_produced, 0), 2) AS defect_rate_pct
FROM ProductionBatch
WHERE defective_units * 50 > units_produced AND units_produced <> 0
ORDER BY 3 DESC, 1← Sensors Silent Through January · Machine Count per Plant From Asset Tags →