How to Implement Window Functions for Cumulative Calculations in SQL

Use the SUM() aggregate with an OVER clause containing ORDER BY and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to compute running totals without collapsing rows.

Window functions are essential tools for data engineers who need to calculate progressive metrics like running totals, moving averages, and cumulative counts. The DataExpert-io/data-engineer-handbook repository provides practical, production-ready examples of how to implement window functions for cumulative calculations in real analytics scenarios.

The Core Pattern for Cumulative Sums

The fundamental syntax for cumulative calculations uses three components in the OVER clause:

• ORDER BY — establishes the sequence for accumulation
• PARTITION BY (optional) — groups rows into independent windows
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — defines the sliding frame from the first row to the current row

This pattern appears throughout the repository's analytical patterns materials, particularly in intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/window_based_analysis.sql.

Basic Cumulative Sum Example

SELECT
    event_date,
    sales,
    SUM(sales) OVER (
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_sales
FROM daily_sales;

The ROWS clause explicitly bounds the window. UNBOUNDED PRECEDING starts from the first row in the partition, and CURRENT ROW includes the present row, producing a true running total.

Partitioned Cumulative Calculations

To calculate separate running totals per group—such as per-user metrics—add PARTITION BY:

SELECT
    user_id,
    event_date,
    clicks,
    SUM(clicks) OVER (
        PARTITION BY user_id
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS user_running_clicks
FROM user_clicks;

Each user_id gets its own independent cumulative counter. This technique is demonstrated in the repository's user growth accounting examples, where user_growth_accounting.sql provides the source tables referenced in the window function analysis.

Beyond SUM: Other Cumulative Aggregates

Window functions support any standard aggregate for cumulative calculations. The AVG() function computes a running average using identical frame syntax:

SELECT
    event_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_avg_revenue
FROM daily_revenue;

Other valid aggregates include COUNT() for running row counts, MIN()/MAX() for cumulative extremes, and STRING_AGG() for concatenated histories (database-dependent).

Frame Specification Shortcuts

Most modern SQL engines recognize ROWS UNBOUNDED PRECEDING as shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The repository examples use the explicit form for clarity, but both are functionally equivalent:

-- Equivalent shorter syntax
SUM(sales) OVER (
    ORDER BY event_date
    ROWS UNBOUNDED PRECEDING
) AS running_sales

Source Files and Learning Path

The Data Engineer Handbook structures its window function materials in intermediate-bootcamp/materials/4-applying-analytical-patterns/:

Path	Purpose
lecture-lab/window_based_analysis.sql	Complete walkthrough of cumulative calculations on user growth data
tables/user_growth_accounting.sql	DDL and sample data for the analysis tables
README.md	Conceptual overview of analytical patterns including window techniques
homework/homework.md	Reinforcement exercises for cumulative window logic

Summary

• Use ORDER BY in the OVER clause to control accumulation sequence
• Add PARTITION BY to compute independent cumulative totals per group
• Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for precise frame control
• Apply any aggregate—SUM, AVG, COUNT, MIN, MAX—to the same window frame
• Reference the repository files window_based_analysis.sql and user_growth_accounting.sql for production-tested patterns

Frequently Asked Questions

What does ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean?

This clause defines a sliding window frame that includes all rows from the beginning of the partition up to and including the current row. UNBOUNDED PRECEDING marks the first row in the partition (or table, if no PARTITION BY exists). CURRENT ROW includes the row being evaluated. Without this explicit frame, some SQL engines default to RANGE mode instead of ROWS, which can produce unexpected results with duplicate ordering values.

Can I use RANGE instead of ROWS for cumulative calculations?

ROWS counts physical row offsets. RANGE counts logical value differences based on the ORDER BY expression. For cumulative totals with unique ordering values, they behave identically. With duplicates in the ordering column, RANGE treats ties as a single group, while ROWS processes each row individually. The repository examples prefer ROWS for predictable, row-by-row accumulation.

How do I reset a cumulative sum at specific intervals?

Use PARTITION BY with a calculated period column. For monthly resetting cumulative totals:

SUM(sales) OVER (
    PARTITION BY DATE_TRUNC('month', event_date)
    ORDER BY event_date
    ROWS UNBOUNDED PRECEDING
) AS monthly_running_sales

Each month becomes a separate partition with its own running total starting from zero.

Do window functions impact query performance significantly?

Window functions compute results in a single pass over the data after sorting, avoiding the self-joins or correlated subqueries required for equivalent results in older SQL. However, large partitions with complex ORDER BY operations may require significant memory. The explicit ROWS frame is generally faster than RANGE because it avoids peer-group detection logic. For performance-critical pipelines, test with your specific dataset and indexing strategy.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}