# How to Implement Window Functions for Cumulative Calculations in SQL

> Learn to implement SQL window functions for cumulative calculations. Easily compute running totals without collapsing rows using SUM OVER with ORDER BY and specific window frames.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-06

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/window_based_analysis.sql).

### Basic Cumulative Sum Example

```sql
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`:

```sql
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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:

```sql
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:

```sql
-- 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/lecture-lab/window_based_analysis.sql) | Complete walkthrough of cumulative calculations on user growth data |
| [`tables/user_growth_accounting.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/tables/user_growth_accounting.sql) | DDL and sample data for the analysis tables |
| [`README.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/README.md) | Conceptual overview of analytical patterns including window techniques |
| [`homework/homework.md`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/window_based_analysis.sql) and [`user_growth_accounting.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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:

```sql
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.