SQL Quality Analytics: Measuring and Reducing Reject Rates with Data

Use PostgreSQL to measure quality performance, calculate reject rates, identify the main defect categories, and discover which production lines and machines deserve investigation.

In a Smart Factory, quality data should not remain a collection of inspection records. The real question is whether SQL can turn those records into evidence about where defects are occurring, how frequently they occur, and which industrial areas deserve attention.

This article uses the real synthetic manufacturing dataset behind the Smart Factory SQL series. The quality table contains 1,143 quality inspections, linked to production orders and machines.

---

1. The industrial question

A production manager rarely asks:

“How many rows are in the quality table?”

Instead, the useful questions are:

• What is the overall reject rate?
• Which production line has the highest defect rate?
• Which defect categories create the most failures?
• Are quality problems concentrated on particular machines?
• Is quality getting better or worse over time?

These are exactly the kinds of questions SQL can answer.

---

2. Understand the quality table

The central table is smart_factory.quality_inspections.

CREATE TABLE smart_factory.quality_inspections (
    inspection_id bigint NOT NULL,
    order_id bigint NOT NULL,
    machine_id integer,
    inspection_timestamp timestamp NOT NULL,
    inspected_quantity integer NOT NULL,
    passed_quantity integer NOT NULL,
    failed_quantity integer NOT NULL,
    defect_code varchar(50),
    defect_category varchar(100),
    inspector varchar(100)
);

The database also enforces an important consistency rule:

passed_quantity + failed_quantity = inspected_quantity

This is valuable because the quality measures are internally coherent. The table is linked to production_orders through order_id and to machines through machine_id.

---

3. Calculate the overall defect rate

Let us begin with the most important KPI:

What percentage of inspected units failed quality inspection?

SELECT
    SUM(inspected_quantity) AS total_inspected,
    SUM(passed_quantity) AS total_passed,
    SUM(failed_quantity) AS total_failed,
    ROUND(
        100.0 * SUM(failed_quantity)
        / NULLIF(SUM(inspected_quantity), 0),
        2
    ) AS defect_rate_pct
FROM smart_factory.quality_inspections;

For this dataset, the query returns:

Metric	Value
Inspected quantity	91,440
Passed quantity	89,103
Failed quantity	2,337
Overall defect rate	2.56%

That 2.56% is our baseline. From here, we can investigate where the problem is concentrated.

---

4. Which production line has the highest defect rate?

Quality records contain a machine identifier. Machines belong to production lines, so we can connect quality data to the factory structure.

SELECT
    pl.line_code,
    pl.line_name,
    SUM(q.inspected_quantity) AS inspected_quantity,
    SUM(q.failed_quantity) AS failed_quantity,
    ROUND(
        100.0 * SUM(q.failed_quantity)
        / NULLIF(SUM(q.inspected_quantity), 0),
        2
    ) AS defect_rate_pct
FROM smart_factory.quality_inspections q
JOIN smart_factory.machines m
    ON m.machine_id = q.machine_id
JOIN smart_factory.production_lines pl
    ON pl.line_id = m.line_id
GROUP BY
    pl.line_code,
    pl.line_name
ORDER BY defect_rate_pct DESC;

The real dataset gives:

Line	Inspected	Failed	Defect rate
L02 — Heavy Machining Line	30,480	1,198	3.93%
L03 — Final Assembly & Packaging	30,480	598	1.96%
L01 — Precision Assembly Line	30,480	541	1.77%

L02 is clearly the first line to investigate.

---

5. What kinds of defects are occurring?

A total defect count is not enough. We need to understand the nature of the defects.

SELECT
    defect_code,
    defect_category,
    SUM(failed_quantity) AS failed_quantity,
    ROUND(
        100.0 * SUM(failed_quantity)
        / NULLIF(SUM(SUM(failed_quantity)) OVER (), 0),
        2
    ) AS pct_of_failed
FROM smart_factory.quality_inspections
GROUP BY defect_code, defect_category
ORDER BY failed_quantity DESC;

The dataset contains four principal defect categories:

Defect	Failed units	Rate among inspected
Visual Defect	965	1.79%
Assembly Error	511	1.90%
Dimensional Deviation	431	8.16%
Surface Finish	430	8.14%

This is an important industrial insight:

Dimensional and surface-finish problems have much higher defect rates than the two larger-volume defect categories.

High frequency and high defect rate are not the same thing. SQL lets us examine both.

---

6. Quality over time

Now we move from a static KPI to time-series analysis.

SELECT
    DATE_TRUNC('month', inspection_timestamp)::date AS month,
    SUM(inspected_quantity) AS inspected_quantity,
    SUM(failed_quantity) AS failed_quantity,
    ROUND(
        100.0 * SUM(failed_quantity)
        / NULLIF(SUM(inspected_quantity), 0),
        2
    ) AS defect_rate_pct
FROM smart_factory.quality_inspections
GROUP BY 1
ORDER BY 1;

Month	Inspected	Failed	Defect rate
January	14,400	254	1.76%
February	14,400	251	1.74%
March	15,840	448	2.83%
April	15,840	693	4.38%
May	15,120	422	2.79%
June	15,840	269	1.70%

April is the clear anomaly in the six-month scenario. The defect rate rises from 1.74% in February to 4.38% in April before returning to 1.70% in June.

---

7. Which machine deserves attention?

Quality inspections in this dataset are concentrated on three machines, one on each production line.

SELECT
    m.machine_code,
    m.machine_name,
    COUNT(*) AS inspections,
    SUM(q.failed_quantity) AS failed_quantity,
    ROUND(
        100.0 * SUM(q.failed_quantity)
        / NULLIF(SUM(q.inspected_quantity), 0),
        2
    ) AS defect_rate_pct
FROM smart_factory.quality_inspections q
JOIN smart_factory.machines m
    ON m.machine_id = q.machine_id
GROUP BY m.machine_code, m.machine_name
ORDER BY defect_rate_pct DESC;

Machine	Inspected	Failed	Defect rate
M004 — CNC Machine B1	30,480	1,198	3.93%
M008 — Assembly Robot C1	30,480	598	1.96%
M001 — Assembly Robot A1	30,480	541	1.77%

M004 is the machine-level priority. Notice that it is the machine on L02, the line that also has the highest defect rate.

---

8. A useful industrial investigation

We can now combine several dimensions:

Quality
   │
   ├── Production Line
   │
   ├── Machine
   │
   ├── Product
   │
   ├── Defect Category
   │
   └── Time
        │
        └── compare with downtime / maintenance

The purpose is not to immediately declare that one machine caused every defect. The purpose is to identify a strong candidate for investigation.

In this dataset, L02 and M004 stand out because their quality performance is materially worse than the other inspected lines and machines.

---

9. SQL challenge

Now investigate the dataset yourself.

1. Calculate the defect rate for each production line.
2. Find the defect category with the highest defect rate, not simply the highest count.
3. Calculate monthly defect-rate changes with LAG().
4. Identify the month with the largest increase in defect rate.
5. Compare M004 quality performance with its maintenance history.
6. Investigate whether downtime and quality deterioration occur in the same periods.

Important: correlation is not causation. A simultaneous increase in downtime and defects is an investigation signal, not proof that downtime caused the defects.

---

10. What you learned

• How to calculate a manufacturing defect rate with SQL.
• How to join quality inspections to machines and production lines.
• How to compare quality performance across industrial dimensions.
• How to analyze defect categories.
• How to build a monthly quality time series.
• How to identify an industrial priority from data.
• Why correlation must be distinguished from causation.

---

Work with the complete Smart Factory dataset

The complete synthetic manufacturing environment contains production orders, machines, production events, downtime, maintenance, quality and energy data, together with PostgreSQL setup files, documentation and SQL exercises.

Smart Factory Manufacturing Dataset v0.1 — $9

→ Get the complete dataset on Gumroad

Smart Factory SQL Series
PostgreSQL • Industrial SQL • Industry 4.0 • Manufacturing Analytics