Analytical SQL Window Functions: OVER, PARTITION BY & Running Metrics

Created: 2026-11-08 · Last updated: 2026-11-08 · Nejat Hakan

Executive Summary: In traditional SQL, aggregating data with GROUP BY collapses multiple individual rows into a single summary tuple per group, irreversibly discarding the distinct identities and non-aggregated attributes of individual records. Window Functions (OVER (...)) fundamentally alter this limitation: they compute running totals, rankings, moving averages, and period-over-period deltas across a sliding partition of rows while preserving the identity of every single row. This guide explores the execution lifecycle of window functions, partition frames, navigation functions, and production data-deduplication patterns.

Preserve Row Identity

Compute partition-wide analytics (e.g., total department salary) alongside individual employee details in a single scan.
Rankings & Bucketing

Differentiate ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() to solve Top-N category queries deterministically.
Temporal Navigation

Look backwards and forwards across time-series data using LAG() and LEAD() without expensive, unindexed self-joins.
Sliding Window Frames

Define moving calculation boundaries: ROWS BETWEEN UNBOUNDED PRECEDING for ledgers and moving N-day rolling averages.

1. The Core Paradigm: `GROUP BY` vs. `OVER (...)`

The distinction between standard grouping and windowing lies in whether rows are collapsed or enriched:

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    SRC["Raw Dataset (100 Rows)<br/>(Orders per Customer)"]
    GRP_OP["GROUP BY customer_id<br/>Collapses Rows into Groups"]
    WIN_OP["OVER (PARTITION BY customer_id)<br/>Evaluates Sliding Window per Group"]
    GRP_RES["Aggregated Output (10 Rows)<br/>Individual Order IDs Lost!"]
    WIN_RES["Enriched Output (100 Rows)<br/>Every Order ID Retained +<br/>Running Metrics Appended!"]

    SRC --> GRP_OP
    SRC --> WIN_OP
    GRP_OP --> GRP_RES
    WIN_OP --> WIN_RES

The SQL Query Execution Lifecycle

Window functions occupy a specific, late phase in the SQL execution pipeline: 1. FROM & JOIN (Build data working set) 2. WHERE (Filter individual rows) 3. GROUP BY (Collapse rows into groups) 4. HAVING (Filter collapsed groups) 5. Window Functions (OVER) (Evaluated across the filtered/grouped result set) 6. SELECT (Project output columns) 7. DISTINCT (Deduplicate output tuples) 8. ORDER BY (Sort final output) 9. LIMIT / OFFSET (Slice result stream)

Why Window Functions Cannot Exist in WHERE Clauses

Because WHERE filters rows in Step 2 before window functions are calculated in Step 5, you cannot write WHERE ROW_NUMBER() OVER (...) = 1. To filter on a window function result, wrap it inside a Common Table Expression (CTE) or subquery!

2. Anatomy of the `OVER` Clause

A window specification consists of three configurable components:

FUNCTION() OVER (
    PARTITION BY partition_column   -- 1. Slices data into logical buckets
    ORDER BY sort_column            -- 2. Sorts rows inside each bucket
    ROWS BETWEEN ... AND ...        -- 3. Sets physical calculation frame boundaries
)

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    PART["Partition: customer_id = 42<br/>(Logical Boundary)"]
    R1["Row 1: Ordered 2026-01-01 | $50 | Running Total: $50"]
    R2["Row 2: Ordered 2026-01-15 | $30 | Running Total: $80"]
    R3["Row 3: Ordered 2026-02-01 | $40 | Running Total: $120<br/>(Current Row: Evaluates Previous + Current)"]

    PART --> R1
    PART --> R2
    PART --> R3

Frame Specification Semantics (`ROWS` vs. `RANGE`)

Cumulative Running Total: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW Sums from the very first row of the partition up to the current row.
Centered Moving Average (3-point window): ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING Calculates the average of the immediate previous row, the current row, and the immediate next row.
Rolling Historical 7-Day Window: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW Calculates a 7-day smoothing average over daily metric records.

3. The Window Function Toolkit

A. Ranking Functions

Function	Handling of Ties	Output Sequence for values `(100, 100, 50)`	Primary Engineering Use Case
`ROW_NUMBER()`	Arbitrary unique integer	`1, 2, 3`	Deduplication (picking latest event per device).
`RANK()`	Equal rank, skips next	`1, 1, 3`	Competitions and leaderboards with point ties.
`DENSE_RANK()`	Equal rank, no gaps	`1, 1, 2`	Top-N categories (e.g., Top 3 distinct product prices).
`NTILE(n)`	Divides rows into n buckets	E.g., `NTILE(4)` for quartiles	Performance percentiles and A/B test cohort slicing.

Before window functions, calculating month-over-month growth required joining a table back against itself on month = prev_month + 1, causing quadratic (O(N²)) query slowdowns. LAG and LEAD eliminate self-joins entirely:

LAG(column, offset, default): Retrieves values from N rows behind the current row within the partition.
LEAD(column, offset, default): Retrieves values from N rows ahead of the current row within the partition.

4. Hands-on Production Lab: Telemetry & Financial Analytics

Execute this complete analytical workshop in psql to calculate running balances, period growth, and server log deduplication:

-- File: /opt/db-ops/window_functions_lab.sql
BEGIN;

-- 1. Financial Ledger with Running Balance & Deltas
CREATE TABLE account_ledger (
    entry_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id  INT NOT NULL,
    booked_at   TIMESTAMPTZ NOT NULL,
    amount_cents BIGINT NOT NULL -- Positive = Credit, Negative = Debit
);

INSERT INTO account_ledger (account_id, booked_at, amount_cents) VALUES
    (101, '2026-11-01 09:00:00Z', 100000),
    (101, '2026-11-02 14:30:00Z', -25000),
    (101, '2026-11-04 11:15:00Z',  40000),
    (102, '2026-11-01 10:00:00Z',  50000),
    (102, '2026-11-03 16:00:00Z', -15000);

-- Query: Running Balance & Prior Transaction Comparison
SELECT 
    account_id,
    booked_at,
    amount_cents / 100.0 AS transaction_eur,
    -- Previous transaction amount for this account
    LAG(amount_cents / 100.0, 1, 0.00) OVER (
        PARTITION BY account_id ORDER BY booked_at
    ) AS prev_transaction_eur,
    -- Running real-time balance
    SUM(amount_cents / 100.0) OVER (
        PARTITION BY account_id 
        ORDER BY booked_at 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance_eur
FROM account_ledger
ORDER BY account_id, booked_at;

-- 2. Deduplication Pattern: Get the Single Most Recent Heartbeat per Server
CREATE TABLE server_heartbeats (
    heartbeat_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    server_node  TEXT NOT NULL,
    cpu_load     NUMERIC(5,2) NOT NULL,
    recorded_at  TIMESTAMPTZ NOT NULL
);

INSERT INTO server_heartbeats (server_node, cpu_load, recorded_at) VALUES
    ('node-alpha', 12.5, '2026-11-08 07:00:00Z'),
    ('node-alpha', 45.2, '2026-11-08 07:05:00Z'),
    ('node-beta',  88.1, '2026-11-08 07:01:00Z'),
    ('node-beta',  22.4, '2026-11-08 07:06:00Z');

-- Isolate the latest telemetry status per server
WITH ranked_heartbeats AS (
    SELECT 
        server_node,
        cpu_load,
        recorded_at,
        ROW_NUMBER() OVER (
            PARTITION BY server_node 
            ORDER BY recorded_at DESC
        ) AS rank_order
    FROM server_heartbeats
)
SELECT server_node, cpu_load, recorded_at
FROM ranked_heartbeats
WHERE rank_order = 1;

COMMIT;

Analytical SQL Window Functions: OVER, PARTITION BY & Running Metrics

1. The Core Paradigm: GROUP BY vs. OVER (...)

The SQL Query Execution Lifecycle

2. Anatomy of the OVER Clause

Frame Specification Semantics (ROWS vs. RANGE)