ReviseAlgo Logo

Window Functions

Running Totals and Partitioning

Calculating cumulative metric flows across dataset splits.

Last Updated: June 12, 2026 • 13 min read

Running totals (cumulative sums) and partitioned aggregates are window functions that compute progressively across ordered rows. SUM() OVER accumulates values row by row. Combined with PARTITION BY, they reset for each group — enabling cumulative revenue per salesperson, balance tracking per account, and quota attainment dashboards.

1. Introduction

While GROUP BY collapses rows into aggregates, running totals keep every row visible while showing the cumulative value up to that point. This is the key difference: aggregation reduces rows, window functions enrich them. Combined with PARTITION BY, running totals reset for each group — like computing a separate running total for each customer or department.

2. Why It Matters

  • Financial reporting: Cumulative revenue, running balance, and quota attainment are all running totals — essential for dashboards and executive reports.
  • Moving averages: Smooth out noisy data with AVG() OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for 7-day moving averages.
  • Inventory tracking: Running stock levels (cumulative inbound minus cumulative outbound) per product per warehouse.
  • Quota tracking: Show each salesperson's progress toward their quarterly target with a running sum of their deals.

3. Real-World Analogy

Think of a savings account. Each deposit or withdrawal is a row. Your bank statement shows every transaction AND a running balance — the cumulative sum after each transaction. If you have multiple accounts, each one has its own running balance (PARTITION BY account). That's exactly what SUM(amount) OVER (PARTITION BY account ORDER BY date) computes.

4. How It Works

  • Running total: SUM(amount) OVER (ORDER BY date) — accumulates from the first row to the current row.
  • Partitioned running total: SUM(amount) OVER (PARTITION BY account ORDER BY date) — resets for each account.
  • Window frames: Control which rows are included. ROWS BETWEEN N PRECEDING AND CURRENT ROW creates moving windows (e.g., 7-day rolling average).
  • Default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — includes all rows from the partition start to the current row.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Cumulative revenue with percentage of total:

7-day moving average of daily orders:

8. Common Mistakes

RANGE vs ROWS frame type

RANGE groups ties together (all rows with the same ORDER BY value are in the same frame). ROWS treats each row individually. For moving averages, always use ROWS to get a precise N-row window.

Missing ORDER BY in running total

Without ORDER BY, SUM() OVER () returns the total sum for every row — not a running total. Always include ORDER BY for cumulative calculations.

Interview Insight

"What's the difference between a running total and a grand total in window functions?" — Running total: SUM(x) OVER (ORDER BY date). Grand total: SUM(x) OVER () (no ORDER BY = all rows in one frame). Both can appear in the same query.

9. Quick Quiz

Q1: What does COUNT(*) OVER () return?

Answer: The total row count of the result set — repeated on every row. It's the window function equivalent of a global COUNT, useful for showing "row 5 of 100" without a separate query.

Q2: How do you compute a 30-day rolling sum?

Answer: SUM(value) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW). Use 29 (not 30) because the current row counts as one of the 30 days.

10. Scenario-Based Challenge

Challenge: Salesperson Quota Tracker

Build a dashboard showing each salesperson's progress toward their quarterly quota:

  1. Use SUM(deal_value) OVER (PARTITION BY salesperson ORDER BY close_date) for cumulative revenue.
  2. Compute quota attainment %: cumulative_revenue / quota * 100.
  3. Add a 7-day moving average of daily deal value to show recent velocity.
  4. Flag salespeople whose cumulative revenue is below 50% of quota at the halfway point of the quarter.

11. Debugging Exercise

Find the bug in this moving average query:

Issues:

  1. RANGE with INTERVAL: This works in PostgreSQL but includes all rows within the 6-day range (not exactly 6 rows). If some days have no sales, the window has fewer than 7 data points. For a fixed 7-row window, use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW (requires no gaps in data).
  2. Missing gaps: If daily_sales has missing dates, neither approach works perfectly. Use GENERATE_SERIES to fill gaps first, then apply the moving average.

12. Interview Questions

Q1: How do you compute a running total that resets per group?

A: Add PARTITION BY: SUM(amount) OVER (PARTITION BY department ORDER BY date). The running total resets to 0 at the start of each department's partition.

Q2: What's the difference between ROWS and RANGE in window frames?

A: ROWS counts physical rows (6 PRECEDING = 6 rows back). RANGE uses value-based boundaries (RANGE 6 PRECEDING = all rows with value within 6 of current). For moving averages, ROWS gives a fixed-size window; RANGE gives a fixed-range window that may include varying row counts.

Q3: Can you show both running total and grand total in the same query?

A: Yes: SUM(amount) OVER (ORDER BY date) AS running, SUM(amount) OVER () AS grand_total. The first accumulates progressively; the second shows the total on every row.

13. Production Considerations

  • Index partition + order columns: A composite index on (partition_col, order_col) avoids sorting during window computation — critical for large tables.
  • Use ROWS for fixed-size windows: Moving averages with ROWS guarantee exactly N rows per window. RANGE may include fewer or more rows depending on data density.
  • Fill gaps before computing: Use GENERATE_SERIES + LEFT JOIN to ensure no missing dates/periods before applying running totals or moving averages.
  • Precompute for dashboards: For frequently-accessed dashboards, materialize running totals in a materialized view or summary table to avoid recomputing on every page load.
  • Multiple window functions: PostgreSQL evaluates multiple OVER clauses efficiently if they share the same PARTITION BY and ORDER BY. Group window functions with identical windows for best performance.