Conditional Running Totals — Resets, Caps, and Flags

Standard running totals accumulate forever. Real business problems often need running totals that reset at certain conditions, cap at a maximum value, or start counting only when a threshold is crossed. These patterns push window functions to their limits — and sometimes require combining them with recursive CTEs.

Running total that resets on a condition

Scenario: compute revenue per customer, but reset the running total at the start of each calendar year.

WITH order_totals AS (
    SELECT
        o.customer_id,
        o.created_at,
        DATE_PART('year', o.created_at)          AS year,
        SUM(oi.quantity * oi.unit_price)          AS order_total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY o.customer_id, o.created_at, DATE_PART('year', o.created_at)
)
SELECT
    customer_id,
    created_at::DATE,
    year,
    order_total,
    SUM(order_total) OVER (
        PARTITION BY customer_id, year     -- partition by BOTH customer AND year
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_spend
FROM order_totals
ORDER BY customer_id, created_at;

Adding year to PARTITION BY resets the running total at year boundaries. This is the simplest form of a conditional reset — the reset condition aligns with the partition boundary.

Running count of consecutive positive months

Scenario: count how many consecutive months in a row a customer has had at least one order — reset to 0 when they have a gap month.

This is a gap-and-island problem combined with a running aggregation:

WITH customer_months AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at) AS month,
        COUNT(DISTINCT order_id)         AS orders
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id, DATE_TRUNC('month', created_at)
),
numbered AS (
    SELECT
        customer_id,
        month,
        orders,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY month) AS rn
    FROM customer_months
),
with_group AS (
    SELECT
        customer_id,
        month,
        orders,
        month - (rn * INTERVAL '1 month') AS streak_group
    FROM numbered
),
streak_lengths AS (
    SELECT
        customer_id,
        streak_group,
        MIN(month) AS streak_start,
        MAX(month) AS streak_end,
        COUNT(*)   AS streak_months
    FROM with_group
    GROUP BY customer_id, streak_group
)
SELECT
    c.name,
    sl.streak_start,
    sl.streak_end,
    sl.streak_months
FROM streak_lengths sl
JOIN customers c ON c.customer_id = sl.customer_id
ORDER BY sl.streak_months DESC, c.name;