SQL Time Series Analytics: Moving Averages, Trends, and Seasonal Patterns

Use PostgreSQL window functions to analyze manufacturing KPIs over time, smooth volatility, detect trends, and understand how factory performance evolves.

A factory database does not only contain isolated records. Production, quality, downtime and energy measurements form time series: values that change from one day to another.

That changes the questions we can ask.

• Is production increasing or decreasing?
• Is the reject rate improving?
• Are downtime events becoming more frequent?
• Is energy consumption following production?
• Is a recent value really unusual, or is it simply daily volatility?

In this article we use the Smart Factory PostgreSQL dataset and introduce one of the most useful SQL techniques for this type of analysis: window functions.

---

1. The Smart Factory data behind the analysis

The dataset contains several connected operational sources:

Source	Records	Purpose
Production orders	1,143	Planned vs actual production
Production events	17,167	Detailed production activity
Quality inspections	1,143	Accepted and failed units
Downtime events	82	Production interruptions
Maintenance events	8	Preventive and corrective maintenance
Energy measurements	2,172	Energy and power measurements

The data therefore allows us to build a time-oriented view of the factory rather than analyzing every table independently.

---

2. Start with daily production

Before using window functions, aggregate the production events by day.

SELECT
    event_timestamp::date AS day,
    SUM(quantity_produced) AS daily_production,
    SUM(quantity_rejected) AS daily_rejected
FROM smart_factory.production_events
GROUP BY event_timestamp::date
ORDER BY day;

This transforms thousands of event-level records into a much more useful daily production series.

Across the complete production-event dataset:

• 91,440 units were produced;
• 2,337 rejected units were recorded;
• the overall event-level reject rate is approximately 2.56%.

---

3. Why a moving average is useful

Daily production is naturally volatile. A large order, a short shutdown, a weekend, or a quality event can produce a temporary peak or valley.

A moving average helps reveal the underlying direction.

PostgreSQL window functions calculate values across related rows while keeping the individual rows in the result. The OVER clause defines the window, and PARTITION BY can divide the calculation into independent groups. citeturn0search1turn0search3

For a seven-day moving average:

WITH daily_production AS (
    SELECT
        event_timestamp::date AS day,
        SUM(quantity_produced) AS daily_production
    FROM smart_factory.production_events
    GROUP BY event_timestamp::date
)
SELECT
    day,
    daily_production,
    ROUND(
        AVG(daily_production) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_production
ORDER BY day;

The important part is:

AVG(...) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

For every day, PostgreSQL keeps the current row and the six preceding rows in the window. The result is a seven-row moving average once enough history exists.

---

4. What does the trend look like?

The monthly production-event totals are:

Month	Produced	Rejected	Reject rate
January	60,284	1,418	2.35%
February	57,845	1,332	2.30%
March	63,429	1,709	2.69%
April	64,310	1,983	3.08%
May	62,390	1,652	2.65%
June	64,751	1,443	2.23%

April is particularly interesting: it combines the highest monthly reject rate with the highest rejected quantity in the production-event data.

---

5. Apply the same technique to quality

A time series becomes much more useful when we calculate the reject rate by day.

WITH daily_quality AS (
    SELECT
        inspection_timestamp::date AS day,
        SUM(inspected_quantity) AS inspected,
        SUM(failed_quantity) AS failed
    FROM smart_factory.quality_inspections
    GROUP BY inspection_timestamp::date
)
SELECT
    day,
    inspected,
    failed,
    ROUND(
        100.0 * failed / NULLIF(inspected, 0),
        2
    ) AS defect_rate_pct,
    ROUND(
        AVG(
            100.0 * failed / NULLIF(inspected, 0)
        ) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_quality
ORDER BY day;

This is an important industrial pattern:

raw quality events
        ↓
daily KPI
        ↓
7-day moving average
        ↓
trend detection
        ↓
process investigation

---

6. Analyze downtime over time

The downtime table contains 82 interruption events. The total recorded downtime is 2,069 minutes, including 1,010 minutes classified as unplanned.

We can build the same daily series:

WITH daily_downtime AS (
    SELECT
        start_time::date AS day,
        SUM(
            EXTRACT(EPOCH FROM (end_time - start_time))
        ) / 60.0 AS downtime_minutes
    FROM smart_factory.downtime_events
    GROUP BY start_time::date
)
SELECT
    day,
    ROUND(downtime_minutes, 2) AS downtime_minutes,
    ROUND(
        AVG(downtime_minutes) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_downtime
ORDER BY day;

Now the maintenance team can distinguish a single bad day from a persistent increase in downtime.

---

7. Energy is another time series

The energy table contains 2,172 measurements. Total recorded energy consumption is 293,796.90 kWh.

Monthly energy consumption is:

Month	Energy (kWh)
January	49,807.86
February	44,869.88
March	51,298.48
April	48,957.17
May	50,726.53
June	48,136.99

The same window-function technique can smooth daily energy consumption:

WITH daily_energy AS (
    SELECT
        measurement_timestamp::date AS day,
        SUM(energy_kwh) AS daily_energy_kwh
    FROM smart_factory.energy_consumption
    GROUP BY measurement_timestamp::date
)
SELECT
    day,
    ROUND(daily_energy_kwh, 2) AS daily_energy_kwh,
    ROUND(
        AVG(daily_energy_kwh) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_energy
ORDER BY day;

---

8. Compare production lines with PARTITION BY

The real power of window functions appears when the same calculation must be performed independently for each production line.

WITH daily_line_production AS (
    SELECT
        pe.event_timestamp::date AS day,
        pl.line_code,
        SUM(pe.quantity_produced) AS daily_production
    FROM smart_factory.production_events pe
    JOIN smart_factory.machines m
        ON m.machine_id = pe.machine_id
    JOIN smart_factory.production_lines pl
        ON pl.line_id = m.line_id
    GROUP BY
        pe.event_timestamp::date,
        pl.line_code
)
SELECT
    day,
    line_code,
    daily_production,
    ROUND(
        AVG(daily_production) OVER (
            PARTITION BY line_code
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_line_production
ORDER BY
    line_code,
    day;

PARTITION BY line_code is the critical addition.

It tells PostgreSQL:

Calculate the moving average separately for each production line.

This avoids mixing L01, L02 and L03 into one factory-wide series.

---

9. Compare the current period with the previous one

Another extremely useful window function is LAG(). It gives access to a previous row in the ordered window. This makes month-to-month or week-to-week comparisons straightforward. citeturn0search3

WITH monthly_production AS (
    SELECT
        DATE_TRUNC(
            'month',
            event_timestamp
        )::date AS month,
        SUM(quantity_produced) AS produced
    FROM smart_factory.production_events
    GROUP BY 1
)
SELECT
    month,
    produced,
    LAG(produced) OVER (
        ORDER BY month
    ) AS previous_month,
    ROUND(
        100.0 * (
            produced
            - LAG(produced) OVER (ORDER BY month)
        )
        / NULLIF(
            LAG(produced) OVER (ORDER BY month),
            0
        ),
        2
    ) AS change_pct
FROM monthly_production
ORDER BY month;

Now SQL can answer a management question directly:

Did production improve or deteriorate compared with the previous month?

---

10. Window functions vs GROUP BY

This distinction is fundamental.

Technique	Typical purpose
GROUP BY	Reduce rows into aggregated groups
AVG() OVER()	Calculate an average while keeping each row
ROW_NUMBER()	Number rows within a partition
RANK()	Rank rows while preserving ties
LAG()	Compare a row with a previous row
LEAD()	Look forward to a subsequent row

PostgreSQL's documentation explicitly distinguishes window calculations from ordinary aggregates: window functions operate across related rows without collapsing those rows into a single output row. citeturn0search1

---

11. A practical Smart Factory time-series workflow

Industrial events
       ↓
Daily / weekly aggregation
       ↓
Window function
       ↓
Moving average / previous period / rank
       ↓
Trend detection
       ↓
Operational interpretation
       ↓
Action

The SQL is only the analytical layer. The important step is connecting the result to a manufacturing question.

---

12. Your SQL challenge

1. Calculate a 14-day moving average of the daily reject rate.
2. Find the 7 days with the highest downtime.
3. Use LAG() to calculate the monthly change in reject rate.
4. Calculate a separate moving average for each production line.
5. Rank the machines by average energy consumption using RANK().
6. Find weeks where energy consumption is at least 20% above the previous week's value.

---

13. What you learned

• How to convert industrial event data into time series.
• How to calculate moving averages with AVG() OVER().
• How to create independent analytical windows with PARTITION BY.
• How to compare periods using LAG().
• How to analyze production, quality, downtime and energy using the same pattern.
• Why window functions are particularly valuable for industrial analytics.

---

Practice with the complete Smart Factory dataset

Go beyond isolated SQL examples. Work with a connected manufacturing dataset covering production, machines, downtime, quality, maintenance and energy.

Smart Factory Manufacturing Dataset v0.1 — $9

→ Get the complete dataset on Gumroad

Smart Factory SQL Series
PostgreSQL • Industrial SQL • Industry 4.0 • Manufacturing Analytics