Production Batches Over Two Percent Defects

Easy
251.

Production Batches Over Two Percent Defects

EasyBasics

MySQL

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

ColumnType
batch_idint
product_codevarchar
units_producedint
defective_unitsint

Primary key: batch_id.

One row per batch; defective_units is never more than units_produced.

Example

The rows must come back in the order shown.

ProductionBatch

batch_idproduct_codeunits_produceddefective_units
501BR-DISC120018
502BR-DISC150030
503GEAR-12T80031
504AXLE-S00
505GEAR-12T75024
506PISTON-80200041

Output

batch_idproduct_codedefect_rate_pct
503GEAR-12T3.88
505GEAR-12T3.2
506PISTON-802.05
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.

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

ColumnType
batch_idint
product_codevarchar
units_producedint
defective_unitsint

Primary key: batch_id.

One row per batch; defective_units is never more than units_produced.

Examples

Example 1

ProductionBatch

batch_idproduct_codeunits_produceddefective_units
501BR-DISC120018
502BR-DISC150030
503GEAR-12T80031
504AXLE-S00
505GEAR-12T75024
506PISTON-80200041

Output

batch_idproduct_codedefect_rate_pct
503GEAR-12T3.88
505GEAR-12T3.2
506PISTON-802.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_id

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