Finding Machine Performance Degradation with SQL

Use PostgreSQL to detect when a machine is gradually moving away from normal production performance.

In a smart factory, machine degradation rarely appears as a single dramatic event.

A machine may continue producing parts while its cycle time becomes less stable, rejection rates increase, and maintenance interventions become more frequent.

The real challenge is therefore not simply to ask: "Did the machine fail?"

It is to ask: "Is this machine performing differently from its normal behavior?"

SQL is surprisingly powerful for answering this question.

The machine we are investigating

For this investigation, we will focus on one machine from our Smart Factory PostgreSQL dataset: M005 — CNC Machine B2.

Machine	Type	Manufacturer	Rated Power	Installation
M005	CNC	DMG MORI	48 kW	2020-02-14

The database also contains production events for each machine, including production quantity, rejected quantity and cycle time. This gives us enough information to construct a simple machine-performance monitoring model directly in SQL.

The machine is particularly interesting because its maintenance history contains a corrective intervention on April 28, 2026: spindle bearing replacement after repeated vibration alarms.

Step 1 — Start with the monthly performance

Before building sophisticated detection logic, we need a baseline. A simple monthly aggregation already tells us whether the machine is behaving consistently.

SELECT
    DATE_TRUNC('month', event_timestamp)::date AS month,
    COUNT(*) AS production_events,
    SUM(quantity_produced) AS quantity_produced,
    SUM(quantity_rejected) AS quantity_rejected,
    ROUND(AVG(cycle_time_sec), 2) AS avg_cycle_time_sec
FROM smart_factory.production_events
WHERE machine_id = 5
GROUP BY DATE_TRUNC('month', event_timestamp)
ORDER BY month;

The query produces the following results from our dataset:

Month	Events	Produced	Rejected	Avg. Cycle Time	Reject Rate
January	458	9,770	223	76.84 s	2.28%
February	450	9,623	230	66.86 s	2.39%
March	441	9,721	457	82.83 s	4.70%
April	486	10,567	737	85.85 s	6.97%
May	447	9,490	429	76.83 s	4.52%
June	534	11,791	279	78.16 s	2.37%

Something changed in March and April

The first important observation is that the machine does not simply produce less.

Instead, several indicators deteriorate simultaneously.

• Average cycle time rises from 66.86 seconds in February to 82.83 seconds in March.
• Average cycle time reaches 85.85 seconds in April.
• The rejection rate increases from approximately 2.4% to 4.7% in March.
• It reaches almost 7% in April.

This is much more interesting than simply looking at production volume. The machine is still producing thousands of parts, but its process quality and cycle performance are deteriorating.

Step 2 — Calculate the rejection rate directly with SQL

We can make the deterioration easier to detect by calculating the rejection rate directly in SQL.

SELECT
    DATE_TRUNC('month', event_timestamp)::date AS month,
    SUM(quantity_produced) AS produced,
    SUM(quantity_rejected) AS rejected,
    ROUND(
        100.0 * SUM(quantity_rejected)
        / NULLIF(SUM(quantity_produced), 0),
        2
    ) AS reject_rate_pct
FROM smart_factory.production_events
WHERE machine_id = 5
GROUP BY DATE_TRUNC('month', event_timestamp)
ORDER BY month;

This gives us a much better operational signal.

Month	Reject Rate	Interpretation
January	2.28%	Normal baseline
February	2.39%	Normal baseline
March	4.70%	Degradation signal
April	6.97%	Strong degradation
May	4.52%	Improvement after intervention
June	2.37%	Return close to baseline

Step 3 — Look at the maintenance history

Now comes the important part of industrial analytics: connecting operational degradation with maintenance events.

The maintenance history for M005 contains three interventions in the dataset.

Date	Type	Description	Parts Cost	Labor Cost
2026-02-10	Preventive	Spindle lubrication and vibration inspection	$320	$180
2026-04-28	Corrective	Spindle bearing replacement after repeated vibration alarms	$1,850	$620
2026-05-12	Preventive	Drive cooling system inspection and filter replacement	$240	$160

This creates a very interesting industrial story.

The strongest degradation occurs in March and April, followed by a corrective maintenance intervention on April 28. After that intervention, average cycle time falls back to approximately 76.8 seconds in May and the rejection rate eventually returns to approximately 2.4% in June.

Step 4 — Measure the improvement after maintenance

We can compare the degraded period with the period immediately following the corrective intervention.

SELECT
    CASE
        WHEN event_timestamp < DATE '2026-04-28'
             THEN 'Before corrective maintenance'
        ELSE 'After corrective maintenance'
    END AS period,

    COUNT(*) AS events,

    ROUND(AVG(cycle_time_sec), 2) AS avg_cycle_time_sec,

    SUM(quantity_rejected) AS rejected_quantity,

    SUM(quantity_produced) AS produced_quantity,

    ROUND(
        100.0 * SUM(quantity_rejected)
        / NULLIF(SUM(quantity_produced), 0),
        2
    ) AS reject_rate_pct

FROM smart_factory.production_events

WHERE machine_id = 5

GROUP BY
    CASE
        WHEN event_timestamp < DATE '2026-04-28'
             THEN 'Before corrective maintenance'
        ELSE 'After corrective maintenance'
    END;

The important lesson is not to treat one metric as sufficient. A machine can show stable production volume while simultaneously showing deterioration in cycle time and quality.

Step 5 — Build a simple degradation indicator

We can go one step further and classify monthly machine behavior.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', event_timestamp)::date AS month,
        AVG(cycle_time_sec) AS avg_cycle_time,
        SUM(quantity_produced) AS produced,
        SUM(quantity_rejected) AS rejected
    FROM smart_factory.production_events
    WHERE machine_id = 5
    GROUP BY DATE_TRUNC('month', event_timestamp)
)

SELECT
    month,
    ROUND(avg_cycle_time, 2) AS avg_cycle_time_sec,

    ROUND(
        100.0 * rejected / NULLIF(produced, 0),
        2
    ) AS reject_rate_pct,

    CASE
        WHEN avg_cycle_time > 80
             AND 100.0 * rejected / NULLIF(produced, 0) > 5
            THEN 'HIGH DEGRADATION'

        WHEN avg_cycle_time > 75
             OR 100.0 * rejected / NULLIF(produced, 0) > 3
            THEN 'WARNING'

        ELSE 'NORMAL'
    END AS performance_status

FROM monthly
ORDER BY month;

This produces a simple operational classification:

Period	Cycle Time	Reject Rate	Status
January	76.84 s	2.28%	WARNING
February	66.86 s	2.39%	NORMAL
March	82.83 s	4.70%	HIGH DEGRADATION
April	85.85 s	6.97%	HIGH DEGRADATION
May	76.83 s	4.52%	WARNING
June	78.16 s	2.37%	WARNING

What did SQL actually discover?

The SQL analysis did not predict a failure. It did something more fundamental: it detected a change in machine behavior.

For M005, the degradation pattern is visible through several complementary signals:

• Cycle time increased substantially during March and April.
• Reject rate increased at the same time.
• The deterioration culminated immediately before a corrective maintenance intervention.
• The machine's indicators improved after the intervention.

This is exactly the type of analysis that can become the foundation of a predictive-maintenance system.

From SQL analytics to predictive maintenance

At this stage, we are still doing descriptive and diagnostic analytics. We are looking backward at what happened.

But the same SQL pipeline can become much more powerful when combined with:

• rolling averages,
• standard deviations,
• moving rejection rates,
• downtime events,
• maintenance history,
• energy consumption,
• and machine-specific baselines.

The objective is then to detect abnormal trends early enough for a maintenance engineer to investigate the machine before a major production problem occurs.

Try it yourself

The complete Smart Factory PostgreSQL dataset used throughout this series is available with the associated SQL schema, data and exercises.

Get the Smart Factory SQL Dataset →

You can also explore the open-source project and database structure on GitHub:

View the Smart Factory SQL Repository →

Final takeaway

Machine degradation is rarely a single number. It is a pattern.

SQL allows us to combine production performance, quality and maintenance information and turn that pattern into something measurable.

In this example, M005 did not suddenly fail. Its behavior changed over time.

That is exactly what industrial analytics should help us detect.

And once those degradation signals are available in PostgreSQL, the next step is to connect them to dashboards, alerts and predictive-maintenance workflows.