PostgreSQL Window Functions: Powerful Analytics with LAG(), LEAD(), RANK(), and More

Take your PostgreSQL analytics to the next level with window functions: compare periods, rank machines, calculate running totals, and uncover performance patterns without losing the detail of individual rows.

In the previous article, we used CTEs to break complex industrial questions into manageable analytical steps.

Now we need another capability:

How can we compare a row with other rows without collapsing the dataset?

For example:

• How much did a machine produce compared with the previous day?
• Which machines are the best performers?
• What is the cumulative production of each production line?
• What is the 7-day moving average?
• When did performance start to deteriorate?

This is where PostgreSQL window functions become extremely useful.

---

1. What makes window functions different?

A traditional GROUP BY reduces many rows into fewer rows.

For example:

SELECT
    machine_id,
    SUM(quantity_produced) AS total_produced
FROM smart_factory.production_events
GROUP BY machine_id;

This is useful, but the individual production events disappear from the result.

A window function works differently. It calculates a value across a related set of rows while keeping the original rows.

SELECT
    machine_id,
    event_timestamp,
    quantity_produced,
    SUM(quantity_produced) OVER (
        PARTITION BY machine_id
    ) AS machine_total
FROM smart_factory.production_events;

Every event remains visible, but each row also receives the total production of its machine.

This distinction is fundamental:

GROUP BY
many rows → fewer rows

WINDOW FUNCTION
many rows → same rows + analytical context

---

2. The Smart Factory dataset

This article uses the same PostgreSQL manufacturing environment as the previous articles.

The dataset 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

The production event table contains the event timestamp, machine, event type, quantity produced, quantity rejected and cycle time. fileciteturn18file1L130-L138

The manufacturing model connects machines to production lines and production events, downtime, maintenance and energy measurements. fileciteturn17file0L11-L28

---

3. PARTITION BY: define the analytical group

The most important concept to understand is PARTITION BY.

Consider this query:

SELECT
    m.machine_code,
    pe.event_timestamp,
    pe.quantity_produced,

    SUM(pe.quantity_produced) OVER (
        PARTITION BY m.machine_code
    ) AS machine_total

FROM smart_factory.production_events pe

JOIN smart_factory.machines m
    ON m.machine_id = pe.machine_id

ORDER BY
    m.machine_code,
    pe.event_timestamp;

PARTITION BY m.machine_code tells PostgreSQL:

Calculate the window independently for each machine.

No rows are removed.

---

4. Running totals with SUM() OVER()

A common industrial question is:

How does cumulative production evolve over time?

First aggregate production by day and machine:

WITH daily_production AS (
    SELECT
        pe.machine_id,
        pe.event_timestamp::date AS production_date,
        SUM(pe.quantity_produced) AS produced_quantity
    FROM smart_factory.production_events pe
    GROUP BY
        pe.machine_id,
        pe.event_timestamp::date
)

SELECT
    machine_id,
    production_date,
    produced_quantity,

    SUM(produced_quantity) OVER (
        PARTITION BY machine_id
        ORDER BY production_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total

FROM daily_production
ORDER BY
    machine_id,
    production_date;

Now each machine has its own cumulative production curve.

This is particularly useful for:

• production monitoring;
• target tracking;
• capacity analysis;
• cumulative output dashboards.

---

5. LAG(): compare with the previous row

Now consider a more interesting question:

Did today's production increase or decrease compared with yesterday?

Use LAG().

WITH daily_production AS (
    SELECT
        machine_id,
        event_timestamp::date AS production_date,
        SUM(quantity_produced) AS produced_quantity
    FROM smart_factory.production_events
    GROUP BY
        machine_id,
        event_timestamp::date
)

SELECT
    machine_id,
    production_date,
    produced_quantity,

    LAG(produced_quantity) OVER (
        PARTITION BY machine_id
        ORDER BY production_date
    ) AS previous_day_quantity

FROM daily_production
ORDER BY
    machine_id,
    production_date;

The first row for each machine naturally has no previous row, so LAG() returns NULL.

---

6. Calculate the day-over-day change

We can now turn the previous value into a business KPI.

WITH daily_production AS (
    SELECT
        machine_id,
        event_timestamp::date AS production_date,
        SUM(quantity_produced) AS produced_quantity
    FROM smart_factory.production_events
    GROUP BY
        machine_id,
        event_timestamp::date
),

comparison AS (
    SELECT
        *,
        LAG(produced_quantity) OVER (
            PARTITION BY machine_id
            ORDER BY production_date
        ) AS previous_day_quantity
    FROM daily_production
)

SELECT
    machine_id,
    production_date,
    produced_quantity,
    previous_day_quantity,

    produced_quantity
        - previous_day_quantity AS quantity_change

FROM comparison
ORDER BY
    machine_id,
    production_date;

Now SQL is no longer simply reporting production.

It is detecting change.

---

7. LEAD(): look forward

LEAD() works in the opposite direction.

It allows us to access the following row.

SELECT
    machine_id,
    event_timestamp::date AS production_date,

    LEAD(event_timestamp::date) OVER (
        PARTITION BY machine_id
        ORDER BY event_timestamp::date
    ) AS next_date

FROM smart_factory.production_events;

Typical industrial applications include:

• comparing the current production period with the next one;
• detecting gaps between events;
• measuring intervals;
• building event-to-event analyses.

---

8. Ranking machines with RANK()

Suppose the production manager asks:

Which machines are the top performers?

First calculate a machine-level KPI, then rank it.

WITH machine_production AS (
    SELECT
        machine_id,
        SUM(quantity_produced) AS total_produced
    FROM smart_factory.production_events
    GROUP BY machine_id
)

SELECT
    machine_id,
    total_produced,

    RANK() OVER (
        ORDER BY total_produced DESC
    ) AS production_rank

FROM machine_production
ORDER BY production_rank;

This gives us a ranking without requiring a complicated procedural loop.

---

9. RANK() versus DENSE_RANK() versus ROW_NUMBER()

These functions look similar, but their behavior differs when values are tied.

Function	Behavior
ROW_NUMBER()	Every row gets a unique sequential number.
RANK()	Ties receive the same rank and create gaps.
DENSE_RANK()	Ties receive the same rank without gaps.

For example:

Value     ROW_NUMBER    RANK    DENSE_RANK

100       1             1       1
100       2             1       1
90        3             3       2
80        4             4       3

The right function depends on the business question.

---

10. PARTITION BY production line

Ranking globally is useful, but manufacturing managers often want to compare machines inside each production line.

WITH machine_production AS (
    SELECT
        m.machine_id,
        m.machine_code,
        pl.line_code,
        SUM(pe.quantity_produced) AS total_produced
    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
        m.machine_id,
        m.machine_code,
        pl.line_code
)

SELECT
    line_code,
    machine_code,
    total_produced,

    RANK() OVER (
        PARTITION BY line_code
        ORDER BY total_produced DESC
    ) AS rank_in_line

FROM machine_production
ORDER BY
    line_code,
    rank_in_line;

Now each production line has its own machine ranking.

---

11. Moving averages

Daily production can be noisy.

A machine may produce 500 units one day and 430 the next. Looking at individual days may hide the underlying trend.

A moving average smooths short-term fluctuations.

WITH daily_production AS (
    SELECT
        machine_id,
        event_timestamp::date AS production_date,
        SUM(quantity_produced) AS produced_quantity
    FROM smart_factory.production_events
    GROUP BY
        machine_id,
        event_timestamp::date
)

SELECT
    machine_id,
    production_date,
    produced_quantity,

    ROUND(
        AVG(produced_quantity) OVER (
            PARTITION BY machine_id
            ORDER BY production_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        )::numeric,
        2
    ) AS moving_avg_7d

FROM daily_production
ORDER BY
    machine_id,
    production_date;

This creates a 7-day moving average.

Moving averages are extremely useful for detecting a sustained deterioration that might be difficult to see in individual daily observations.

---

12. Window functions and cycle time

The production event table contains cycle_time_sec, which gives us another useful signal for machine performance. fileciteturn18file1L130-L138

For example, compare a machine's current cycle time with its previous event:

SELECT
    m.machine_code,
    pe.event_timestamp,
    pe.cycle_time_sec,

    LAG(pe.cycle_time_sec) OVER (
        PARTITION BY m.machine_id
        ORDER BY pe.event_timestamp
    ) AS previous_cycle_time

FROM smart_factory.production_events pe

JOIN smart_factory.machines m
    ON m.machine_id = pe.machine_id

WHERE pe.cycle_time_sec IS NOT NULL

ORDER BY
    m.machine_code,
    pe.event_timestamp;

Then calculate:

cycle_time_sec - previous_cycle_time
    AS cycle_time_change

This creates a simple mechanism for identifying sudden changes in machine behavior.

---

13. Window functions + quality

Quality inspections contain inspected, passed and failed quantities, with the database enforcing the relationship between them. fileciteturn18file5L647-L664

We can therefore calculate daily defect rates and compare them with previous periods.

WITH daily_quality AS (
    SELECT
        qi.machine_id,
        qi.inspection_timestamp::date AS inspection_date,
        SUM(qi.inspected_quantity) AS inspected_qty,
        SUM(qi.failed_quantity) AS failed_qty
    FROM smart_factory.quality_inspections qi
    GROUP BY
        qi.machine_id,
        qi.inspection_timestamp::date
),

quality_rates AS (
    SELECT
        *,
        100.0 * failed_qty
            / NULLIF(inspected_qty, 0) AS defect_rate_pct
    FROM daily_quality
)

SELECT
    machine_id,
    inspection_date,
    ROUND(defect_rate_pct::numeric, 2) AS defect_rate_pct,

    LAG(defect_rate_pct) OVER (
        PARTITION BY machine_id
        ORDER BY inspection_date
    ) AS previous_defect_rate

FROM quality_rates
ORDER BY
    machine_id,
    inspection_date;

Now we can ask whether quality is deteriorating rather than simply measuring quality at one point in time.

---

14. A real industrial investigation pattern

Imagine that a machine begins showing longer cycle times.

We can combine several window-function techniques:

Cycle time
     ↓
LAG()
     ↓
Detect change
     ↓
Moving average
     ↓
Confirm trend
     ↓
Compare quality
     ↓
Compare downtime
     ↓
Inspect maintenance

This is much closer to the way industrial analysts actually investigate equipment behavior.

The SQL query becomes part of an analytical workflow.

---

15. Window functions do not replace GROUP BY

This is an important distinction.

Use GROUP BY when you want to change the granularity of the result.

Use window functions when you want to calculate something across related rows without losing those rows.

GROUP BY
→ summarize

WINDOW FUNCTION
→ compare / rank / accumulate / smooth

In real Industrial SQL, the two techniques are often used together.

---

16. Your Smart Factory SQL challenge

1. Calculate daily production for every machine.
2. Use LAG() to calculate the day-over-day production change.
3. Calculate a 7-day moving average.
4. Rank machines by total production.
5. Rank machines separately inside each production line.
6. Calculate a running total of production by line.
7. Calculate a daily reject rate and compare it with the previous day.
8. Compare current cycle time with the previous cycle time.
9. Identify machines with sustained deterioration rather than a single abnormal value.

Bonus challenge: combine your window-function analysis with the CTE techniques from Article 10 and build a single machine-performance investigation.

---

17. What you learned

• OVER() and the concept of a window.
• PARTITION BY for independent analytical groups.
• LAG() for previous-row comparisons.
• LEAD() for forward comparisons.
• ROW_NUMBER(), RANK() and DENSE_RANK().
• Running totals with SUM() OVER().
• Moving averages with AVG() OVER().
• Combining CTEs and window functions for industrial investigations.

Practice with the complete Smart Factory dataset

The complete PostgreSQL dataset gives you the production events, machines, downtime, maintenance, quality and energy data needed to reproduce these industrial SQL investigations.

Smart Factory Manufacturing Dataset v0.1 — $9

→ GET THE COMPLETE DATASET ON GUMROAD

Smart Factory SQL Series — Article 11
PostgreSQL • Window Functions • Industrial SQL • Manufacturing Analytics • Industry 4.0