Running Totals and Cohort Metrics

Cumulative Sums and the Frame Clause

A running total is the sum of all values from the start of a partition up to and including the current row. Window functions make this trivial with the right frame clause — but the frame clause is where most people get stuck, because the default behavior is not what you expect.

When you write SUM(amount) OVER (ORDER BY date) without an explicit frame, the database uses the default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This produces a running total — but with a subtle trap. The RANGE mode groups rows with identical ORDER BY values into a single peer group, and the frame includes the entire peer group. If two rows have the same date, both get the sum of both rows, not a progressive accumulation.

-- Potential issue with duplicate dates
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

-- If two rows share 2024-01-01 with revenue 100 and 200:
-- Both rows show running_total = 300 (not 100 then 300)

The fix is to use ROWS instead of RANGE. ROWS counts physical rows, not logical peer groups:

SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total

With ROWS, each row gets its own position in the frame, so the running total accumulates one row at a time even when dates are identical. The rule: use ROWS when you want row-by-row accumulation; use RANGE when you want peer-group semantics (all tied rows treated as one).

Partitioned running totals. Add PARTITION BY to reset the accumulation at group boundaries:

SELECT
    customer_id,
    order_date,
    total,
    SUM(total) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_running_total
FROM orders;

Each customer's running total starts at zero and grows with their own orders. The partition boundary resets the frame.

Frame clause syntax. The general form is:

ROWS BETWEEN <start> AND <end>

Where each boundary can be:

• UNBOUNDED PRECEDING — the first row of the partition
• N PRECEDING — N rows before the current row
• CURRENT ROW — the current row
• N FOLLOWING — N rows after the current row
• UNBOUNDED FOLLOWING — the last row of the partition

This syntax lets you define any sliding window: the last 7 days, the last 12 months, the current row plus the next 3. The next chapter uses this for moving averages.

Moving Averages and Smoothing

A moving average smooths a time-series by averaging each point with its neighbors. In SQL, this is a window function with a symmetric frame clause that extends both before and after the current row.

The 7-day moving average of daily revenue:

SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS moving_avg_7day
FROM daily_sales;

The frame ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING includes the current row, the 3 rows before it, and the 3 rows after it — 7 rows total. Each output value is the average of that window. This smooths out day-of-week effects and random noise, revealing the underlying trend.

Edge behavior. At the boundaries of the partition, the frame is truncated. The first row has no preceding rows, so its moving average is computed over fewer than 7 rows (just the current row and the 3 following). This is usually acceptable, but if you need a strict 7-day average that returns NULL at the boundaries, add a CASE expression:

CASE
    WHEN ROW_NUMBER() OVER (ORDER BY order_date) >= 4
     AND ROW_NUMBER() OVER (ORDER BY order_date) <= COUNT(*) OVER () - 3
    THEN AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    )
END AS strict_moving_avg

This returns NULL for the first 3 and last 3 rows, where a full 7-day window is not available.

Centered vs trailing averages. The example above is a centered moving average (equal rows before and after). A trailing moving average uses only past data:

AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS trailing_7day_avg

The trailing version is more common in business reporting because it only uses data available at the current point in time — you cannot know the future. A centered average is useful for visualization and analysis where the full dataset is already available.

Weighted moving averages. A simple moving average weights all rows equally. If you want recent rows to count more, you need a different approach — either a self-join with weights, or an exponential moving average computed recursively. Most databases do not have a built-in weighted moving average function, so the simple frame-based average is the standard tool.

Multiple window functions in one query. You can compute several moving averages with different window sizes in a single SELECT:

SELECT
    order_date,
    daily_revenue,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_30d,
    AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW) AS avg_90d
FROM daily_sales;

This produces a trend analysis table with short, medium, and long-term averages side by side. When the 7-day average crosses above the 30-day average, analysts call it a "golden cross" — a potential upward trend signal.

Cohort Analysis: Grouping by First Touch

Cohort analysis groups users by a shared characteristic — typically their first month of activity — and tracks how each group behaves over time. The foundational step is computing each user's first-touch date, which is a window function with MIN as the aggregate.

SELECT
    user_id,
    event_date,
    MIN(event_date) OVER (PARTITION BY user_id) AS first_touch_date
FROM events;

MIN(event_date) OVER (PARTITION BY user_id) finds the earliest event date for each user and attaches it to every row for that user. This is the first-touch pattern — the same deduplication logic from the ranking chapter, but using MIN instead of ROW_NUMBER.

Once you have the first-touch date, you can assign each user to a cohort:

WITH user_cohort AS (
    SELECT
        user_id,
        MIN(event_date) OVER (PARTITION BY user_id) AS first_touch_date
    FROM events
),
cohort_assigned AS (
    SELECT
        user_id,
        first_touch_date,
        DATE_TRUNC('month', first_touch_date) AS cohort_month
    FROM user_cohort
    GROUP BY user_id, first_touch_date
)
SELECT * FROM cohort_assigned;

The DATE_TRUNC('month', first_touch_date) converts a date like 2024-01-15 to 2024-01-01, grouping all January users into the same cohort. The GROUP BY collapses the per-event rows to one row per user.

Retention by cohort. The core cohort metric is retention: what fraction of users from each cohort were active in each subsequent month?

WITH user_cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(event_date) OVER (PARTITION BY user_id)) AS cohort_month
    FROM events
    GROUP BY user_id
),
user_activity AS (
    SELECT
        uc.user_id,
        uc.cohort_month,
        DATE_TRUNC('month', e.event_date) AS activity_month
    FROM user_cohort uc
    JOIN events e ON e.user_id = uc.user_id
),
retention AS (
    SELECT
        cohort_month,
        activity_month,
        COUNT(DISTINCT user_id) AS active_users,
        PERIOD_DIFF(
            EXTRACT(YEAR FROM activity_month) * 12 + EXTRACT(MONTH FROM activity_month),
            EXTRACT(YEAR FROM cohort_month) * 12 + EXTRACT(MONTH FROM cohort_month)
        ) AS months_since_signup
    FROM user_activity
    GROUP BY cohort_month, activity_month
)
SELECT
    r.cohort_month,
    r.months_since_signup,
    r.active_users,
    ROUND(100.0 * r.active_users / FIRST_VALUE(r.active_users) OVER (
        PARTITION BY r.cohort_month ORDER BY r.months_since_signup
    ), 2) AS retention_pct
FROM retention r
ORDER BY r.cohort_month, r.months_since_signup;

The months_since_signup column computes the gap between the cohort month and the activity month. The FIRST_VALUE window function retrieves the cohort's month-0 active user count (the denominator), and the retention percentage is the ratio of active users at each month to the baseline.

Cohort size normalization. The retention percentage is only meaningful relative to the cohort size. A cohort of 10 users with 5 retained shows 50% retention; a cohort of 10,000 with 5,000 retained also shows 50%. The percentage normalizes for cohort size, which is why it is the standard metric rather than raw counts.

Advanced Cohort Metrics: LTV and Decay Curves

Beyond retention, cohort analysis extends to revenue metrics — lifetime value (LTV), average revenue per user (ARPU), and revenue decay curves. All of these build on the same foundation: assign users to cohorts, then aggregate within each cohort over time.

Cohort LTV. The cumulative revenue per user in each cohort, tracked over months since signup:

WITH user_cohort AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(order_date) OVER (PARTITION BY user_id)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
monthly_revenue AS (
    SELECT
        uc.cohort_month,
        DATE_TRUNC('month', o.order_date) AS order_month,
        SUM(o.total) AS revenue,
        COUNT(DISTINCT o.user_id) AS paying_users
    FROM user_cohort uc
    JOIN orders o ON o.user_id = uc.user_id
    GROUP BY uc.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
    cohort_month,
    order_month,
    revenue,
    paying_users,
    SUM(revenue) OVER (
        PARTITION BY cohort_month
        ORDER BY order_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    ROUND(
        SUM(revenue) OVER (
            PARTITION BY cohort_month
            ORDER BY order_month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) / NULLIF(COUNT(*) OVER (PARTITION BY cohort_month), 0),
    2) AS arpu
FROM monthly_revenue;

The SUM(revenue) OVER (...) computes cumulative revenue per cohort. The arpu column divides cumulative revenue by the number of months elapsed — a simplified ARPU that assumes all cohort users are still active. For a precise ARPU, divide by the cohort size (total users who joined), not the number of months.

Decay curves. Retention naturally decays over time. The decay rate — how quickly retention drops — is a key metric for product health. Compute it as the ratio of retention at month N to retention at month N-1:

WITH retention AS (
    -- ... retention calculation from previous chapter ...
)
SELECT
    cohort_month,
    months_since_signup,
    retention_pct,
    LAG(retention_pct) OVER (PARTITION BY cohort_month ORDER BY months_since_signup) AS prev_month_retention,
    ROUND(
        100.0 * retention_pct / NULLIF(LAG(retention_pct) OVER (
            PARTITION BY cohort_month ORDER BY months_since_signup
        ), 0),
    2) AS decay_rate
FROM retention;

A decay rate of 80% means retention dropped by 20% from the previous month. A stable product shows a decay rate that converges to a plateau (e.g., 95% month-over-month after month 3). A declining product shows a decay rate that continues to fall.

Cohort comparison. The ultimate goal of cohort analysis is comparing cohorts. Did the January cohort retain better than the February cohort? Did a product change in March improve retention for subsequent cohorts? These comparisons require the full retention table as a intermediate result, then a pivot or visualization tool to display it. In SQL, the retention table is the final output — the pivot is typically done in a BI tool or a follow-up query with conditional aggregation:

SELECT
    cohort_month,
    MAX(CASE WHEN months_since_signup = 0 THEN retention_pct END) AS month_0,
    MAX(CASE WHEN months_since_signup = 1 THEN retention_pct END) AS month_1,
    MAX(CASE WHEN months_since_signup = 3 THEN retention_pct END) AS month_3,
    MAX(CASE WHEN months_since_signup = 6 THEN retention_pct END) AS month_6,
    MAX(CASE WHEN months_since_signup = 12 THEN retention_pct END) AS month_12
FROM retention_table
GROUP BY cohort_month
ORDER BY cohort_month;

This produces the classic cohort retention grid — rows are cohorts, columns are months since signup, cells are retention percentages. It is the single most important output in subscription business analytics.