Window Functions for Running Totals and Moving Averages

1. Calculating Running Totals

A running total (or cumulative sum) calculates the sum of a value from the start of a sequence up to the current row. It's used everywhere, from tracking cumulative sales in a quarter to monitoring the total number of user sign-ups over time.

In the last lesson, our window functions operated on the entire partition. To calculate a running total, we need to tell SQL to create a window that starts at the first row and ends at the current row. This is done using a frame clause within the OVER() clause.

The explicit way to define this frame is with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Let's break that down:

• ROWS: Specifies that the frame is defined by a physical number of rows.
• UNBOUNDED PRECEDING: The frame starts at the very first row of the partition.
• CURRENT ROW: The frame ends at the row currently being processed.

This article provides a clear explanation and examples.

As you saw in the article, when ORDER BY is used, SQL often assumes ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as the default frame for aggregate functions like SUM(). This means that a simpler query often works for running totals:

SELECT
  SaleDate,
  SaleAmount,
  SUM(SaleAmount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales;

While this shortcut is convenient, explicitly writing the full ROWS BETWEEN... clause makes your query clearer and is good practice, especially as you start building more complex frames.

2. Computing Moving Averages

Running totals are great for tracking cumulative growth, but sometimes we need to understand recent trends and smooth out day-to-day volatility. This is where moving averages (also called rolling averages) come in. A moving average calculates the average of a value over a specific number of recent periods, like a 7-day or 30-day moving average.

To do this, we just need to adjust our window frame. Instead of an UNBOUNDED start, we specify a fixed number of rows to look back. For a 3-day moving average, the frame would include the current day and the two preceding days.

The syntax for this is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

This video provides an excellent, step-by-step guide to calculating rolling values.

As the video demonstrated, this technique is incredibly versatile. By simply changing the aggregate function (SUM, AVG, MIN, MAX, COUNT) or the frame definition, you can answer a wide range of analytical questions.

For example, to calculate a 7-day moving average of temperature, partitioned by city, you would write:

SELECT
  city,
  date,
  temperature,
  AVG(temperature) OVER (
    PARTITION BY city
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7_day
FROM weather_data;

3. A Quick but Important Detail: ROWS vs. RANGE

So far, we've only used ROWS to define our frame. There is another keyword, RANGE, that can also be used. While they often produce the same result, they behave differently when there are ties in the ORDER BY column.

• ROWS: Defines the frame based on a fixed number of physical rows before and after the current row. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING always includes exactly three rows (if available).
• RANGE: Defines the frame based on a logical range of values relative to the value in the current row's ordering column. RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING on a price column would include all rows where the price is within 1 unit of the current row's price. If multiple rows have the same price, they are all included.

For most running total and moving average tasks based on sequential data like dates, ROWS is what you want. RANGE is more specialized, for example, if you wanted to find the average price of all items within $10 of the current item's price.

This image provides a great visual comparison.

Understanding this distinction will help you write more precise queries and avoid unexpected results when dealing with data that contains duplicate values in your ordering column.

4. Practice Exercises

Let's apply what you've learned. Consider the following table, daily_revenue, which tracks revenue for two different online services.

Problem 1: Calculate the cumulative revenue for each service

Task: For each service, calculate the running total of revenue, ordered by date.

Desired Output:

SELECT
  service,
  date,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY service
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_revenue;

Problem 2: Calculate the 3-day moving average revenue

Task: For each service, calculate the 3-day moving average of revenue. For the first two days of each service, the average will be based on the available data (1 and 2 days, respectively).

Desired Output:

SELECT
  service,
  date,
  revenue,
  -- We cast to numeric/decimal to ensure floating-point division
  ROUND(
    AVG(revenue) OVER (
      PARTITION BY service
      ORDER BY date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2
  ) AS moving_avg_3day
FROM daily_revenue;

Conclusion

You've now added another crucial tool to your SQL analytics toolkit. By defining explicit window frames, you can move beyond static, whole-partition calculations and perform sophisticated sequential analysis.

Key Takeaways:

• A window frame defines a specific subset of rows within a partition for a window function to operate on.
• Running totals are calculated using a frame that grows with each row, typically ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Moving averages are calculated using a sliding frame of a fixed size, like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-day average.
• The same frame clause syntax can be used with SUM, AVG, COUNT, MIN, and MAX to compute various rolling metrics.
• ROWS defines a frame by physical row count, which is what you'll use most often for time-series analysis.

In our next lesson, we'll shift gears and explore how to use CASE statements, both on their own and within aggregate functions. This will enable you to implement conditional logic, create custom categories, and even pivot data from a "long" to a "wide" format—another powerful technique for creating summary reports.