Advanced SQL with CTEs: Complex Industrial Analysis Made Simple

Use PostgreSQL Common Table Expressions (CTEs) to break complex industrial questions into readable steps and combine production, downtime, quality, maintenance, and energy data.

Industrial SQL becomes difficult when one question requires several intermediate calculations.

For example:

Which production line combines strong output, low downtime, acceptable quality, and reasonable energy consumption?

Answering that question may require several aggregations and several joins. Putting everything into one enormous SELECT statement quickly becomes hard to read, debug, and maintain.

This is where Common Table Expressions (CTEs) become extremely useful.

---

1. What is a CTE?

A CTE is a named query defined with the WITH clause. PostgreSQL then allows the following query to refer to that named result as if it were a temporary query result for the statement.

The basic structure is:

WITH my_cte AS (
    SELECT
        ...
    FROM ...
)
SELECT
    ...
FROM my_cte;

The important idea is not the syntax itself.

The important idea is that we can divide one complex industrial problem into small analytical steps.

---

2. Why CTEs are useful in Smart Factory analytics

The dataset contains nine connected operational tables inside the smart_factory schema. The documented environment contains:

• 3 production lines
• 12 machines
• 5 products
• 1,143 production orders
• 17,167 production events
• 82 downtime events
• 8 maintenance events
• 1,143 quality inspections
• 2,172 energy measurements

These counts are defined in the dataset README and can be verified directly after installation. fileciteturn16file0L63-L155

The schema also deliberately connects production events and operational events through machine and order relationships. fileciteturn16file3L647-L704

---

3. Start with one simple CTE

Suppose we want to know the actual production generated by each production line.

The dataset already contains the direct relationship between production orders and production lines.

WITH line_production AS (
    SELECT
        pl.line_id,
        pl.line_code,
        pl.line_name,
        SUM(po.actual_quantity) AS actual_quantity
    FROM smart_factory.production_orders po
    JOIN smart_factory.production_lines pl
        ON pl.line_id = po.line_id
    GROUP BY
        pl.line_id,
        pl.line_code,
        pl.line_name
)
SELECT
    line_code,
    line_name,
    actual_quantity
FROM line_production
ORDER BY actual_quantity DESC;

Instead of thinking about the entire query at once, think:

CTE 1
↓
Calculate production by line

Final SELECT
↓
Sort the result

---

4. Build a downtime CTE

Now create a second analytical step.

We want total downtime by machine.

WITH machine_downtime AS (
    SELECT
        machine_id,
        SUM(
            EXTRACT(
                EPOCH FROM (
                    COALESCE(end_time, start_time)
                    - start_time
                )
            )
        ) / 60.0 AS downtime_minutes
    FROM smart_factory.downtime_events
    GROUP BY machine_id
)
SELECT
    machine_id,
    ROUND(downtime_minutes, 2) AS downtime_minutes
FROM machine_downtime
ORDER BY downtime_minutes DESC;

The CTE isolates the downtime calculation. We can now reuse that result in a larger query without rewriting the aggregation.

---

5. Combine CTEs

Now the real advantage becomes visible.

We can create one CTE for production and another for downtime, then combine them.

WITH line_production AS (
    SELECT
        pl.line_id,
        pl.line_code,
        pl.line_name,
        SUM(po.actual_quantity) AS actual_quantity
    FROM smart_factory.production_orders po
    JOIN smart_factory.production_lines pl
        ON pl.line_id = po.line_id
    GROUP BY
        pl.line_id,
        pl.line_code,
        pl.line_name
),
line_downtime AS (
    SELECT
        m.line_id,
        SUM(
            EXTRACT(
                EPOCH FROM (
                    COALESCE(d.end_time, d.start_time)
                    - d.start_time
                )
            )
        ) / 60.0 AS downtime_minutes
    FROM smart_factory.downtime_events d
    JOIN smart_factory.machines m
        ON m.machine_id = d.machine_id
    GROUP BY m.line_id
)
SELECT
    p.line_code,
    p.line_name,
    p.actual_quantity,
    ROUND(d.downtime_minutes, 2) AS downtime_minutes
FROM line_production p
LEFT JOIN line_downtime d
    ON d.line_id = p.line_id
ORDER BY p.actual_quantity DESC;

Now we have a small production-performance dataset containing two KPIs.

---

6. Add quality

Quality inspections are linked to production orders and machines in the schema, which makes them another natural CTE for the investigation. fileciteturn16file3L692-L704

quality_by_line AS (
    SELECT
        po.line_id,
        SUM(qi.inspected_quantity) AS inspected_quantity,
        SUM(qi.failed_quantity) AS failed_quantity
    FROM smart_factory.quality_inspections qi
    JOIN smart_factory.production_orders po
        ON po.order_id = qi.order_id
    GROUP BY po.line_id
)

Then calculate the reject rate:

ROUND(
    100.0 * failed_quantity
    / NULLIF(inspected_quantity, 0),
    2
) AS reject_rate_pct

Notice the use of NULLIF(). It prevents a division-by-zero error when no inspected quantity exists for a group.

---

7. Add energy

Energy measurements are linked to machines, and machines are linked to production lines. The dump explicitly defines these foreign-key relationships. fileciteturn16file4L770-L798

That lets us create another CTE:

energy_by_line AS (
    SELECT
        m.line_id,
        SUM(e.energy_kwh) AS energy_kwh
    FROM smart_factory.energy_consumption e
    JOIN smart_factory.machines m
        ON m.machine_id = e.machine_id
    GROUP BY m.line_id
)

We now have four independent analytical steps:

line_production
       ↓
line_downtime
       ↓
quality_by_line
       ↓
energy_by_line
       ↓
FINAL SELECT

---

8. Build the complete industrial KPI query

Now we can assemble the pieces.

WITH line_production AS (
    SELECT
        pl.line_id,
        pl.line_code,
        pl.line_name,
        SUM(po.planned_quantity) AS planned_quantity,
        SUM(po.actual_quantity) AS actual_quantity
    FROM smart_factory.production_orders po
    JOIN smart_factory.production_lines pl
        ON pl.line_id = po.line_id
    GROUP BY
        pl.line_id,
        pl.line_code,
        pl.line_name
),

line_downtime AS (
    SELECT
        m.line_id,
        SUM(
            EXTRACT(
                EPOCH FROM (
                    COALESCE(d.end_time, d.start_time)
                    - d.start_time
                )
            )
        ) / 60.0 AS downtime_minutes
    FROM smart_factory.downtime_events d
    JOIN smart_factory.machines m
        ON m.machine_id = d.machine_id
    GROUP BY m.line_id
),

quality_by_line AS (
    SELECT
        po.line_id,
        SUM(qi.inspected_quantity) AS inspected_quantity,
        SUM(qi.failed_quantity) AS failed_quantity
    FROM smart_factory.quality_inspections qi
    JOIN smart_factory.production_orders po
        ON po.order_id = qi.order_id
    GROUP BY po.line_id
),

energy_by_line AS (
    SELECT
        m.line_id,
        SUM(e.energy_kwh) AS energy_kwh
    FROM smart_factory.energy_consumption e
    JOIN smart_factory.machines m
        ON m.machine_id = e.machine_id
    GROUP BY m.line_id
)

SELECT
    p.line_code,
    p.line_name,
    p.planned_quantity,
    p.actual_quantity,

    ROUND(
        100.0 * p.actual_quantity
        / NULLIF(p.planned_quantity, 0),
        2
    ) AS fulfillment_rate_pct,

    ROUND(d.downtime_minutes, 2)
        AS downtime_minutes,

    ROUND(
        100.0 * q.failed_quantity
        / NULLIF(q.inspected_quantity, 0),
        2
    ) AS reject_rate_pct,

    ROUND(e.energy_kwh, 2)
        AS energy_kwh

FROM line_production p

LEFT JOIN line_downtime d
    ON d.line_id = p.line_id

LEFT JOIN quality_by_line q
    ON q.line_id = p.line_id

LEFT JOIN energy_by_line e
    ON e.line_id = p.line_id

ORDER BY fulfillment_rate_pct DESC;

This query is much easier to understand because every CTE has one responsibility.

---

9. Think like an industrial analyst

The final table can now answer several questions at once:

• Which line produces the most?
• Which line has the best fulfillment rate?
• Which line accumulates the most downtime?
• Which line has the highest reject rate?
• Which line consumes the most energy?

But the important analytical step comes next:

Do not stop at the KPI.
Use the KPI to decide where the next investigation should go.

---

10. CTEs for anomaly investigation

Suppose a production line has a poor fulfillment rate. We can create a first CTE that identifies the line, then another that identifies its machines, then another that examines downtime.

WITH line_performance AS (
    SELECT
        line_id,
        SUM(planned_quantity) AS planned,
        SUM(actual_quantity) AS actual
    FROM smart_factory.production_orders
    GROUP BY line_id
),

underperforming_lines AS (
    SELECT
        line_id,
        planned,
        actual,
        100.0 * actual / NULLIF(planned, 0)
            AS fulfillment_rate
    FROM line_performance
    WHERE actual < planned
)

SELECT
    pl.line_code,
    pl.line_name,
    u.fulfillment_rate
FROM underperforming_lines u
JOIN smart_factory.production_lines pl
    ON pl.line_id = u.line_id
ORDER BY u.fulfillment_rate;

The query reads almost like a sequence of analytical instructions:

1. Calculate line performance
2. Keep underperforming lines
3. Join them to descriptive information
4. Rank the problem areas

---

11. CTEs are especially useful for debugging

A large SQL query can fail for many reasons: incorrect joins, duplicate rows, wrong aggregation levels, NULL values, or incorrect filters.

With CTEs, you can test each stage independently.

WITH step_1 AS (...),
step_2 AS (...),
step_3 AS (...)

SELECT *
FROM step_2;

Before writing the final SELECT, inspect the intermediate result.

Ask:

• Are the row counts correct?
• Are the joins multiplying rows?
• Are NULL values expected?
• Is the aggregation at the correct grain?
• Do the numbers make sense operationally?

This is particularly important in manufacturing because an incorrect join can silently inflate production, downtime or energy totals.

---

12. CTEs and the industrial data model

The Smart Factory database was designed so that operational tables are connected through production lines, machines, products and production orders. fileciteturn16file0L47-L59

For example:

production_lines
       │
       └── machines
              │
              ├── production_events
              ├── downtime_events
              ├── maintenance_events
              └── energy_consumption

production_orders
       │
       ├── production_events
       └── quality_inspections

CTEs give us a practical way to turn this relational structure into an analytical pipeline.

---

13. CTEs vs one giant query

One giant query	CTE approach
Harder to read	Logical steps are visible
Difficult to debug	Each stage can be tested
Repeated calculations	Intermediate results can be reused
Easy to lose the analytical grain	Each CTE can have a defined grain

CTEs do not automatically make every query faster. Their major benefit here is clarity, modularity and analytical control.

---

14. Your SQL challenge

1. Create a CTE that calculates production by production line.
2. Create a second CTE that calculates total downtime by line.
3. Add a third CTE for quality and reject rate.
4. Add a fourth CTE for energy consumption.
5. Join all four CTEs into one industrial KPI table.
6. Calculate fulfillment rate with NULLIF().
7. Rank the lines from best to worst fulfillment rate.
8. Identify the line that deserves the next investigation.

Bonus challenge: add maintenance cost as a fifth CTE and compare maintenance spending with downtime.

---

15. What you learned

• How to write a CTE using WITH.
• How to split a complex industrial question into analytical stages.
• How to combine multiple CTEs.
• How to control the aggregation grain of each step.
• How to combine production, downtime, quality and energy KPIs.
• How CTEs make complex SQL easier to inspect and maintain.
• How to use SQL as an investigation workflow rather than a collection of isolated queries.

---

Practice with the complete Smart Factory dataset

The complete PostgreSQL dataset contains the interconnected manufacturing tables used throughout this SQL series, together with the database dump, generator, documentation, CSV files and exercises.

Smart Factory Manufacturing Dataset v0.1 — $9

→ GET THE COMPLETE DATASET ON GUMROAD

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