SQL Maintenance Analytics: Is Preventive Maintenance Actually Working?

Analyze maintenance events, compare preventive and corrective work, measure maintenance cost, and investigate whether interventions actually reduce machine downtime with PostgreSQL.

In the previous article, we investigated where production stops.

But once a machine becomes a recurring source of downtime, the next question is obvious:

Is maintenance actually improving machine performance?

A maintenance database can tell us much more than when a technician visited a machine.

With SQL, we can investigate:

• how many maintenance interventions occurred;
• preventive versus corrective maintenance;
• maintenance duration;
• parts and labor cost;
• which machines consume the most maintenance effort;
• whether downtime changes after an intervention.

In this article, we will use the real data from the Smart Factory Manufacturing Dataset v0.1.

---

1. The industrial question

Imagine that you are responsible for maintenance in a manufacturing plant.

Your team performs preventive inspections every month. Technicians also repair machines when failures occur.

Management asks three simple questions:

1. Are we doing enough preventive maintenance?
2. Which machines are costing us the most?
3. Does maintenance actually reduce downtime?

The third question is particularly important.

A maintenance intervention costs money and takes a machine out of production. It should therefore have an operational purpose.

SQL gives us a way to measure that impact instead of relying only on intuition.

---

2. Understanding the maintenance table

The Smart Factory database contains a dedicated table:

smart_factory.maintenance_events

The table contains:

• machine_id — the affected machine;
• maintenance_type — preventive or corrective;
• start_time and end_time;
• technician;
• description;
• parts_cost;
• labor_cost.

The database documentation confirms that maintenance interventions are explicitly classified as preventive or corrective and include cost information.

That means we can investigate both maintenance activity and maintenance economics with SQL.

---

3. How much maintenance is happening?

Let's begin with the simplest question:

How many maintenance interventions do we have?

SELECT
    COUNT(*) AS maintenance_events
FROM smart_factory.maintenance_events;

The dataset contains 8 maintenance interventions.

That number alone is not very informative.

We need to distinguish between planned preventive work and reactive corrective work.

---

4. Preventive vs corrective maintenance

Let's group the interventions by maintenance type.

SELECT
    maintenance_type,
    COUNT(*) AS maintenance_events
FROM smart_factory.maintenance_events
GROUP BY maintenance_type
ORDER BY maintenance_events DESC;

The real dataset gives us:

Maintenance type	Events	Share
PREVENTIVE	6	75%
CORRECTIVE	2	25%

So, in terms of number of interventions, preventive maintenance dominates the dataset.

But there is another question:

Does preventive maintenance also dominate maintenance cost?

---

5. Calculating maintenance cost with SQL

Each maintenance intervention contains two cost components:

• parts cost;
• labor cost.

We can combine them directly in SQL.

SELECT
    maintenance_type,
    COUNT(*) AS maintenance_events,
    ROUND(SUM(parts_cost + labor_cost), 2) AS total_cost
FROM smart_factory.maintenance_events
GROUP BY maintenance_type
ORDER BY total_cost DESC;

The actual dataset produces:

Type	Events	Cost
PREVENTIVE	6	2,370 DH
CORRECTIVE	2	3,590 DH
TOTAL	8	5,960 DH

This is our first interesting finding.

Corrective maintenance represents only 25% of interventions but 60.2% of total maintenance cost.

This is precisely the type of relationship that SQL can reveal quickly.

---

6. How long does maintenance take?

Cost is only one dimension.

A maintenance intervention also removes productive capacity from the factory.

We can calculate its duration using PostgreSQL timestamp arithmetic.

SELECT
    maintenance_type,
    COUNT(*) AS events,
    ROUND(
        SUM(
            EXTRACT(
                EPOCH FROM (end_time - start_time)
            ) / 3600.0
        )::numeric,
        2
    ) AS maintenance_hours,
    ROUND(
        AVG(
            EXTRACT(
                EPOCH FROM (end_time - start_time)
            ) / 3600.0
        )::numeric,
        2
    ) AS average_hours
FROM smart_factory.maintenance_events
GROUP BY maintenance_type;

The dataset contains:

• 15.5 hours of preventive maintenance;
• 13.0 hours of corrective maintenance;
• 28.5 hours of total maintenance activity.

But the average duration tells an even more interesting story:

• Preventive intervention: 2.58 hours on average;
• Corrective intervention: 6.50 hours on average.

Corrective work is therefore substantially longer per intervention.

---

7. Which machine costs the most?

Maintenance strategy becomes much more useful when we connect interventions to machines.

SELECT
    m.machine_code,
    m.machine_name,
    COUNT(me.maintenance_id) AS maintenance_events,
    ROUND(SUM(me.parts_cost + me.labor_cost), 2) AS maintenance_cost
FROM smart_factory.maintenance_events me
JOIN smart_factory.machines m
    ON m.machine_id = me.machine_id
GROUP BY
    m.machine_code,
    m.machine_name
ORDER BY maintenance_cost DESC;

The most important machine in the maintenance dataset is M005, the CNC Machine B2 on Line 2.

M005 has:

• 3 maintenance interventions;
• 14.5 hours of maintenance time;
• 3,370 DH of maintenance cost.

That makes M005 our natural investigation target.

---

8. The M005 investigation

M005 is particularly interesting because the downtime data contains repeated spindle-vibration failures.

The dataset records four SPINDLE-VIB downtime events before the corrective intervention, including events on March 21, March 30, April 11 and April 23.

The maintenance history then records:

1. February 10: preventive spindle lubrication and vibration inspection;
2. April 28: corrective spindle bearing replacement after repeated vibration alarms;
3. May 12: preventive drive cooling inspection and filter replacement.

These maintenance records are directly present in the database dump.

This gives us something much more interesting than a generic SQL exercise:

We can investigate a real industrial story encoded in the dataset.

---

9. Did the corrective intervention reduce downtime?

Now we can test the most important hypothesis.

The corrective intervention took place on April 28, 2026, from 09:00 to 17:00.

Before that intervention, M005 accumulated:

• 459 minutes of total downtime;
• including 451 minutes of unplanned downtime.

After the corrective intervention, the remaining M005 downtime in the dataset is:

• 52 minutes total;
• including only 14 minutes of unplanned downtime;
• plus one planned 38-minute maintenance stop.

That is a dramatic change in the observed downtime pattern.

We can calculate this type of analysis directly from the downtime table.

SELECT
    CASE
        WHEN start_time < TIMESTAMP '2026-04-28 09:00:00'
            THEN 'Before corrective maintenance'
        ELSE 'After corrective maintenance'
    END AS period,
    COUNT(*) AS downtime_events,
    ROUND(
        SUM(
            EXTRACT(EPOCH FROM (end_time - start_time)) / 60.0
        )::numeric,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events
WHERE machine_id = 5
GROUP BY period
ORDER BY period;

The result is:

Period	Events	Downtime
Before corrective maintenance	8	459 min
After corrective maintenance	2	52 min

Most importantly, the repeated spindle-vibration failures disappear from the later period.

---

10. Did maintenance definitely cause the improvement?

This is where we need to be careful.

SQL shows us a strong temporal relationship:

Repeated vibration failures
        ↓
Corrective maintenance
        ↓
Spindle bearing replacement
        ↓
Much lower observed downtime

But this is an observational dataset.

We should therefore avoid claiming that SQL has mathematically proven causality.

A stronger industrial analysis would also compare:

• cycle time before and after maintenance;
• production quantity;
• unplanned downtime;
• energy consumption;
• quality indicators where data is available;
• the same machine over a comparable time window.

This is an important lesson in Industrial Data Analytics:

SQL can reveal evidence. Industrial analysis still requires interpretation.

---

11. A reusable maintenance KPI query

Instead of writing a separate query every time, we can build a reusable machine-level maintenance summary.

SELECT
    m.machine_code,
    m.machine_name,
    COUNT(me.maintenance_id) AS maintenance_events,
    COUNT(*) FILTER (
        WHERE me.maintenance_type = 'PREVENTIVE'
    ) AS preventive_events,
    COUNT(*) FILTER (
        WHERE me.maintenance_type = 'CORRECTIVE'
    ) AS corrective_events,
    ROUND(SUM(me.parts_cost + me.labor_cost), 2)
        AS total_maintenance_cost,
    ROUND(
        SUM(
            EXTRACT(EPOCH FROM (me.end_time - me.start_time))
            / 3600.0
        )::numeric,
        2
    ) AS maintenance_hours
FROM smart_factory.machines m
JOIN smart_factory.maintenance_events me
    ON me.machine_id = m.machine_id
GROUP BY
    m.machine_code,
    m.machine_name
ORDER BY total_maintenance_cost DESC;

Now the same query can become the foundation for a dashboard or a maintenance KPI report.

---

12. What we learned

We started with a simple question:

Is preventive maintenance actually working?

SQL allowed us to move through several analytical levels:

1. count maintenance events;
2. separate preventive and corrective work;
3. calculate maintenance duration;
4. calculate maintenance cost;
5. rank machines by maintenance cost;
6. identify M005 as an investigation target;
7. connect maintenance with downtime;
8. compare the machine before and after a corrective intervention.

That is the progression we want throughout this Smart Factory SQL series:

SQL syntax
   ↓
Industrial KPI
   ↓
Machine investigation
   ↓
Evidence
   ↓
Industrial decision

---

13. Your Smart Factory SQL challenge

Now investigate the dataset yourself.

Challenge 1 — Maintenance cost ranking

Which machine has the highest total maintenance cost?

Challenge 2 — Preventive vs corrective

For each machine, calculate the percentage of interventions that were corrective.

Challenge 3 — Maintenance duration

Which machine has the highest average maintenance duration?

Challenge 4 — Maintenance impact

Find machines where unplanned downtime decreases after a corrective intervention.

Do not look for a predefined answer. Let the data lead you to the conclusion.

---

Ready to investigate the factory yourself?

The Smart Factory Manufacturing Dataset v0.1 contains the complete synthetic PostgreSQL environment used for these Industrial SQL investigations.

• PostgreSQL 17 database dump
• 1,143 production orders
• 17,167 production events
• 82 downtime events
• 8 maintenance events
• 1,143 quality inspections
• 2,172 energy measurements
• 3 production lines
• 12 machines
• 5 products
• Data Dictionary and schema documentation
• Starter SQL queries and investigation scenario

Smart Factory Manufacturing Dataset v0.1 — $9

→ Get the complete dataset on Gumroad

---

What comes next?

We have now investigated:

• production performance;
• machine downtime;
• maintenance activity.

The next question is naturally about the output of the factory:

Is production quality getting worse?

That will take us into quality inspections, passed and failed quantities, reject rates, defect categories and machine-level quality analysis.

Next article: SQL Quality Analytics: Measuring and Reducing Reject Rates

Smart Factory Manufacturing Dataset v0.1 • PostgreSQL 17 • Industrial SQL • Industry 4.0 • Manufacturing Analytics