Leverage PostgreSQL window functions to uncover downtime patterns, spot increases early and focus improvement efforts where they matter most.

Using Window Functions to Analyze Downtime Trends Over Time

Smart Factory SQL Series — Article 16

Downtime is not just a number. In a Smart Factory, it is a time-dependent signal. A machine may have acceptable downtime today while quietly deteriorating over several weeks.

In Article 15, we investigated machine performance degradation and identified M005 — CNC Machine B2 as a machine requiring attention. In this article, we move one level deeper: instead of asking only how much downtime occurred, we ask how downtime evolves over time.

We will use PostgreSQL window functions to calculate monthly trends, moving averages, period-over-period changes and machine rankings without losing the underlying analytical rows.

1. The industrial question

Downtime does not stay constant. It evolves.

Which machine shows an increasing downtime trend, and when did that trend change?

Machine	Name	Type	Line
M005	CNC Machine B2	CNC	L02

Why M005? M005 showed performance degradation in Article 15. Now we quantify the downtime trend and examine the impact of the corrective intervention.

2. Downtime per month

First, we aggregate downtime events by month and machine.

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

For M005, the monthly result is:

Month	Downtime events	Downtime minutes
2026-01	2	95.00
2026-02	3	142.00
2026-03	6	231.00
2026-04	9	436.00
2026-05	3	118.00
2026-06	2	72.00

First signal: downtime rises from 95 minutes in January to 436 minutes in April — the worst month in the observed period. It then falls sharply after the corrective maintenance intervention.

3. Moving average of downtime

A single monthly value can be noisy. A moving average smooths short-term fluctuations and helps reveal the underlying direction.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', start_time)::date AS month,
        machine_id,
        ROUND(
            SUM(
                EXTRACT(
                    EPOCH FROM (
                        COALESCE(end_time, NOW()) - start_time
                    )
                ) / 60.0
            )::numeric,
            2
        ) AS downtime_minutes
    FROM smart_factory.downtime_events
    GROUP BY
        DATE_TRUNC('month', start_time),
        machine_id
)
SELECT
    month,
    downtime_minutes,
    ROUND(
        AVG(downtime_minutes) OVER (
            PARTITION BY machine_id
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )::numeric,
        2
    ) AS moving_avg_3m
FROM monthly
WHERE machine_id = 5
ORDER BY month;

Month	Downtime	3-month moving average
2026-01	95.00	95.00
2026-02	142.00	118.50
2026-03	231.00	156.00
2026-04	436.00	269.67
2026-05	118.00	261.67
2026-06	72.00	208.67

4. Compare with the previous month using LAG()

The next question is even more operational:

How much did downtime increase or decrease compared with the previous month?

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', start_time)::date AS month,
        machine_id,
        ROUND(
            SUM(
                EXTRACT(
                    EPOCH FROM (
                        COALESCE(end_time, NOW()) - start_time
                    )
                ) / 60.0
            )::numeric,
            2
        ) AS downtime_minutes
    FROM smart_factory.downtime_events
    GROUP BY
        DATE_TRUNC('month', start_time),
        machine_id
)
SELECT
    month,
    downtime_minutes,
    downtime_minutes
      - LAG(downtime_minutes) OVER (
            PARTITION BY machine_id
            ORDER BY month
        ) AS diff_vs_previous_month
FROM monthly
WHERE machine_id = 5
ORDER BY month;

Month	Downtime	Change vs previous month
2026-01	95.00	NULL
2026-02	142.00	+47.00
2026-03	231.00	+89.00
2026-04	436.00	+205.00
2026-05	118.00	-318.00
2026-06	72.00	-46.00

The industrial signal is now obvious. April shows a +205 minute jump compared with March. After the April 28 corrective intervention, downtime falls by 318 minutes in May and another 46 minutes in June.

5. Rank machines by average downtime

Trend analysis tells us what happens to one machine. We also need to know whether that machine is an outlier compared with the rest of the factory.

WITH last_6_months AS (
    SELECT *
    FROM smart_factory.downtime_events
    WHERE start_time >= (
        SELECT MAX(start_time) - INTERVAL '6 months'
        FROM smart_factory.downtime_events
    )
),
per_machine AS (
    SELECT
        machine_id,
        ROUND(
            AVG(
                EXTRACT(
                    EPOCH FROM (
                        COALESCE(end_time, NOW()) - start_time
                    )
                )
            ) / 60.0,
            2
        ) AS avg_downtime_min
    FROM last_6_months
    GROUP BY machine_id
)
SELECT
    machine_id,
    avg_downtime_min,
    RANK() OVER (
        ORDER BY avg_downtime_min DESC
    ) AS downtime_rank
FROM per_machine
ORDER BY downtime_rank;

Machine	Average downtime (min)	Rank
5 — M005	182.33	1
3 — M003	97.40	2
7 — M007	88.17	3
2 — M002	61.25	4

M005 ranks first for average downtime. This confirms that the machine is not merely experiencing one isolated abnormal month.

6. What do the window functions actually add?

Question	Technique	Industrial use
What happened last month?	LAG()	Period-over-period comparison
What is the recent trend?	AVG() OVER()	Moving average
Which machine is worst?	RANK()	Prioritization
What is the running total?	SUM() OVER()	Cumulative production/downtime
Which machine is first inside each line?	PARTITION BY + ranking	Line-level benchmarking

GROUP BY summarizes rows. Window functions compare, rank, accumulate and smooth values without losing the analytical rows.

7. From SQL result to maintenance decision

• M005's downtime increased progressively from January through April.
• April reached 436 minutes.
• The April month-to-month increase was +205 minutes.
• The three-month moving average peaked around the same period.
• Downtime fell sharply in May.
• Downtime fell again in June.
• M005 ranks first for average downtime over the six-month period.

The dataset also records a corrective maintenance intervention on April 28 involving the spindle bearing after repeated vibration alarms.

degradation → increasing downtime → corrective intervention → operational recovery

This is not a causal proof by itself. It is a strong operational signal that deserves investigation using maintenance and sensor data.

8. A reusable industrial SQL workflow

Industrial events
       ↓
Daily / monthly aggregation
       ↓
Window function
       ↓
Moving average / LAG / RANK
       ↓
Trend detection
       ↓
Operational interpretation
       ↓
Maintenance action

9. Your Smart Factory SQL Challenge

1. Calculate daily downtime for every machine.
2. Use LAG() to calculate day-over-day downtime changes.
3. Calculate a 7-day moving average of downtime.
4. Rank machines by total downtime.
5. Rank machines separately inside each production line using PARTITION BY.
6. Calculate a running cumulative downtime total by machine.
7. Compare monthly reject rate with the previous month.
8. Identify machines with sustained deterioration rather than one isolated abnormal value.
9. Combine the window-function analysis with the CTE techniques from earlier articles.

Bonus challenge: build a single SQL query that flags a machine when its current downtime exceeds its three-month moving average by more than 20%.

10. What You Learned

• OVER() and analytical windows
• PARTITION BY for machine or line-level analysis
• LAG() for previous-period comparisons
• RANK() for industrial prioritization
• Moving averages with AVG() OVER()
• Trend analysis without collapsing the underlying rows
• Connecting SQL trends to maintenance decisions
• Preparing industrial data for KPI dashboards and predictive-maintenance workflows

Practice with the complete Smart Factory dataset

Stop practicing Industrial SQL on generic customer and sales databases.

Work with the connected synthetic manufacturing environment used throughout this series:

• 9 core tables
• 20,000+ industrial records
• PostgreSQL 17 SQL scripts
• CSV files
• Documentation
• Real manufacturing story

Smart Factory Manufacturing Dataset v0.1 — $9

→ GET THE COMPLETE DATASET ON GUMROAD

Explore the project on GitHub

Explore the companion project, database schema and SQL examples:

→ Smart Factory SQL on GitHub

---

← Previous: Article 15 — Finding Machine Performance Degradation with SQL

Next →: Article 17 — Advanced Window Functions for OEE Analysis

Smart Factory SQL Series — Article 16
PostgreSQL • Window Functions • LAG() • RANK() • Moving Averages • Downtime Analytics • Industrial SQL • Industry 4.0