SQL Correlation Analysis: Discover Hidden Relationships Between Factory KPIs

Use PostgreSQL to compute correlations between production, downtime, maintenance, quality, and energy KPIs—and turn connected factory data into better questions.

A Smart Factory rarely has isolated problems.

A production slowdown can be associated with machine downtime. Downtime can be associated with quality losses. Maintenance activity can increase after repeated stoppages. Energy consumption may move with production volume.

The interesting question is therefore not only what happened? but also:

Which industrial variables tend to move together?

In this article we use PostgreSQL correlation analysis to investigate relationships between manufacturing KPIs.

---

1. What does correlation mean?

Correlation measures the strength and direction of a relationship between two variables. Pearson's correlation coefficient, commonly written as r, ranges from -1 to +1.

r	Interpretation
Close to +1	Strong positive relationship
Close to 0	Little or no linear relationship
Close to -1	Strong negative relationship

Important: correlation does not prove causation. A strong correlation tells us that two variables move together; it does not prove that one variable causes the other.

---

2. The manufacturing data

Our Smart Factory dataset contains several operational sources that can be combined for KPI analysis:

• 1,143 production orders
• 17,167 production events
• 1,143 quality inspections
• 82 downtime events
• 8 maintenance events
• 2,172 energy measurements

Across the production-event data, the dataset contains 91,440 produced units and 2,337 rejected units, corresponding to an overall reject rate of approximately 2.56%.

The challenge is that these measurements live in different operational tables. Before calculating correlations, we therefore need to create a common analytical grain.

---

3. Why we need a common time grain

Production events may occur many times per day, while downtime and energy measurements have different timestamps and frequencies.

Comparing raw rows directly would therefore be misleading.

A practical industrial approach is:

Raw operational data
        ↓
Common time grain
        ↓
Daily / weekly / monthly KPIs
        ↓
Correlation analysis
        ↓
Investigation of possible relationships

For this article we use monthly KPIs because the objective is to observe broader operational relationships rather than individual machine events.

---

4. Build a monthly KPI dataset

The first step is to aggregate the production-event data by month.

WITH monthly_production AS (
    SELECT
        DATE_TRUNC('month', event_timestamp)::date AS month,
        SUM(quantity_produced) AS produced_qty,
        SUM(quantity_rejected) AS rejected_qty
    FROM smart_factory.production_events
    GROUP BY 1
)
SELECT
    month,
    produced_qty,
    rejected_qty,
    ROUND(
        100.0 * rejected_qty / NULLIF(produced_qty, 0),
        2
    ) AS reject_rate_pct
FROM monthly_production
ORDER BY month;

Now production and quality have the same monthly grain.

---

5. Add downtime

Downtime is stored as events with a start and end timestamp. We can convert each event into minutes and aggregate it by month.

SELECT
    DATE_TRUNC('month', start_time)::date AS month,
    ROUND(
        SUM(
            EXTRACT(
                EPOCH FROM (
                    COALESCE(end_time, start_time)
                    - start_time
                )
            )
        ) / 60.0,
        2
    ) AS downtime_minutes
FROM smart_factory.downtime_events
GROUP BY 1
ORDER BY 1;

The dataset contains 82 downtime events and approximately 2,069 minutes of recorded downtime.

---

6. Add maintenance and energy

We can use the same monthly grain for maintenance and energy.

-- Monthly maintenance cost
SELECT
    DATE_TRUNC('month', start_time)::date AS month,
    SUM(cost) AS maintenance_cost
FROM smart_factory.maintenance_events
GROUP BY 1
ORDER BY 1;

-- Monthly energy consumption
SELECT
    DATE_TRUNC('month', measurement_timestamp)::date AS month,
    SUM(energy_kwh) AS energy_kwh
FROM smart_factory.energy_consumption
GROUP BY 1
ORDER BY 1;

The energy table contains 2,172 measurements and approximately 293,796.90 kWh of recorded consumption.

---

7. Combine the KPIs

Once every source has been aggregated to the same month, we can join the KPI series.

WITH monthly_production AS (
    SELECT
        DATE_TRUNC('month', event_timestamp)::date AS month,
        SUM(quantity_produced) AS produced_qty,
        SUM(quantity_rejected) AS rejected_qty
    FROM smart_factory.production_events
    GROUP BY 1
),
monthly_downtime AS (
    SELECT
        DATE_TRUNC('month', start_time)::date AS month,
        SUM(
            EXTRACT(
                EPOCH FROM (
                    COALESCE(end_time, start_time)
                    - start_time
                )
            )
        ) / 60.0 AS downtime_min
    FROM smart_factory.downtime_events
    GROUP BY 1
),
monthly_energy AS (
    SELECT
        DATE_TRUNC('month', measurement_timestamp)::date AS month,
        SUM(energy_kwh) AS energy_kwh
    FROM smart_factory.energy_consumption
    GROUP BY 1
)
SELECT
    p.month,
    p.produced_qty,
    p.rejected_qty,
    ROUND(
        100.0 * p.rejected_qty
        / NULLIF(p.produced_qty, 0),
        2
    ) AS reject_rate_pct,
    d.downtime_min,
    e.energy_kwh
FROM monthly_production p
LEFT JOIN monthly_downtime d
    ON d.month = p.month
LEFT JOIN monthly_energy e
    ON e.month = p.month
ORDER BY p.month;

This intermediate dataset is the key analytical object: one row represents one month, and each column represents a manufacturing KPI.

---

8. Calculate a correlation in PostgreSQL

PostgreSQL provides the corr() aggregate function for calculating the Pearson correlation coefficient between two numeric expressions.

WITH monthly_kpis AS (
    -- build the common monthly KPI dataset here
)
SELECT
    CORR(downtime_min, reject_rate_pct)
        AS downtime_vs_reject_rate
FROM monthly_kpis;

The result is a single coefficient.

For example:

downtime_vs_reject_rate
-----------------------
0.84

A coefficient around +0.84 would indicate a strong positive linear relationship in the analyzed sample: months with more downtime also tend to have higher reject rates.

This is an analytical signal, not proof of causation. The next step should be to investigate which machines, products, shifts or downtime reasons are behind the relationship.

---

9. Build a correlation matrix

Once the monthly KPI dataset exists, we can calculate several relationships at once.

SELECT
    CORR(produced_qty, rejected_qty)
        AS production_vs_rejections,

    CORR(produced_qty, energy_kwh)
        AS production_vs_energy,

    CORR(downtime_min, reject_rate_pct)
        AS downtime_vs_reject_rate,

    CORR(downtime_min, maintenance_cost)
        AS downtime_vs_maintenance,

    CORR(maintenance_cost, reject_rate_pct)
        AS maintenance_vs_reject_rate

FROM monthly_kpis;

This gives management a compact first view of how the major KPIs move together.

---

10. What the dataset suggests

The monthly production-event data shows a particularly interesting period in April: rejected production rises to approximately 3.08%, while the monthly rejected quantity reaches 1,983 units.

That makes April an excellent candidate for investigation.

The analytical workflow should therefore move from:

Correlation
    ↓
Identify abnormal month
    ↓
Identify affected machines
    ↓
Inspect downtime reasons
    ↓
Inspect maintenance history
    ↓
Inspect quality events
    ↓
Find the operational explanation

This is where SQL becomes an industrial investigation tool rather than merely a query language.

---

11. Correlation is not causation

Suppose downtime and reject rate have a strong positive correlation.

Several explanations remain possible:

• downtime causes unstable production conditions;
• the same machine problem causes both downtime and defects;
• a third factor affects both variables;
• the relationship is specific to one product or production line;
• the apparent relationship is amplified by a small number of observations.

Therefore, correlation should be treated as a hypothesis-generation mechanism.

The next SQL queries should drill down to machines, production lines, products, downtime categories and maintenance events.

---

12. Go from factory-level correlation to machine-level analysis

A factory-wide correlation can hide an important fact: only one machine may be responsible for most of the relationship.

For example, start by ranking machines according to downtime:

SELECT
    m.machine_code,
    m.machine_name,
    ROUND(
        SUM(
            EXTRACT(
                EPOCH FROM (
                    COALESCE(d.end_time, d.start_time)
                    - d.start_time
                )
            )
        ) / 60.0,
        2
    ) AS downtime_minutes
FROM smart_factory.machines m
JOIN smart_factory.downtime_events d
    ON d.machine_id = m.machine_id
GROUP BY
    m.machine_code,
    m.machine_name
ORDER BY downtime_minutes DESC;

Now the investigation can focus on the machines that actually matter.

---

13. Your SQL challenge

1. Build the complete monthly KPI dataset.
2. Calculate the correlation between downtime and reject rate.
3. Calculate the correlation between production and energy consumption.
4. Calculate the correlation between downtime and maintenance cost.
5. Identify the month with the highest reject rate.
6. Identify the machines responsible for the highest downtime.
7. Compare the problematic machines with their quality results.
8. Investigate whether the relationship remains when the analysis is performed separately for each production line.

---

14. What you learned

• Why industrial KPIs must be brought to a common analytical grain.
• How to use PostgreSQL's CORR() function.
• How to construct a monthly manufacturing KPI dataset.
• How to investigate relationships between downtime, quality, maintenance, production and energy.
• Why correlation is useful for discovering hypotheses.
• Why correlation must not be interpreted as causation.
• How to move from a factory-level signal to machine-level investigation.

---

Practice with the complete Smart Factory dataset

Instead of working with artificial customer and sales tables, investigate a connected manufacturing environment containing production, machines, downtime, quality, maintenance and energy data.

Smart Factory Manufacturing Dataset v0.1 — $9

→ GET THE COMPLETE DATASET ON GUMROAD

Smart Factory SQL Series — Article 09
PostgreSQL • Industrial SQL • Industry 4.0 • Manufacturing Analytics