Window Functions for Trend Analysis: Running Totals and Moving Averages

Running Totals vs. Rolling Totals

Before we write any code, it's essential to understand the difference between two key concepts: running totals and rolling totals.

• A Running Total (or cumulative total) is a sum that accumulates from the very beginning of a dataset (or partition) up to the current row. It always expands.
• A Rolling Total (or moving total) is a sum over a fixed-size "sliding" window of rows, like "the last 7 days". As a new day is added, the oldest day is dropped.

Calculating a Running Total

Let's start with the running total. This is often used to track cumulative metrics like year-to-date sales or total user growth.

In the last lesson, you used OVER (ORDER BY ...) for ranking. When you combine an aggregate function like SUM() with ORDER BY, SQL defaults to calculating a running total.

The syntax is straightforward:
SUM(column_to_sum) OVER (ORDER BY ordering_column)

Why does this work? Because SQL applies a default frame clause: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This tells the function to include all rows from the start of the partition (UNBOUNDED PRECEDING) up to and including the current row.

Let's see a practical example.

Now, let's visualize this process. The video from "Data with Baraa" that you started earlier has a fantastic animation showing how the frame for a running total expands with each row.

Defining Your Window: The Frame Clause

The default frame is great for running totals, but what if you want a moving average over a fixed window (e.g., the last 3 days)? For that, you need to define the frame yourself using the ROWS BETWEEN clause.

This clause explicitly defines the upper and lower boundaries of the window relative to the current row.

The most common options are:

• CURRENT ROW: The row being processed.
• n PRECEDING: n rows before the current row.
• n FOLLOWING: n rows after the current row.
• UNBOUNDED PRECEDING: All rows before the current row (the start of the partition).
• UNBOUNDED FOLLOWING: All rows after the current row (the end of the partition).

So, a 3-day moving average including the current day would use the frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. This creates a window of 3 rows (2 before + 1 current).

Calculating Moving Averages and Rolling Totals

A moving average is simply a rolling total divided by the number of periods. In SQL, you use the AVG() function over a sliding window. This is incredibly useful in business to smooth out noisy data (like daily sales) and identify the real underlying trend.

Let's watch the video again to see how a rolling total with a fixed frame contrasts with the running total we just saw.

Now, let's put this into practice by calculating a moving average and reinforcing the PARTITION BY clause you learned in the last lesson.

As you can see, the syntax builds logically on what you already know:

1. Use AVG() or SUM() to specify the calculation.
2. Use PARTITION BY to create separate groups (optional, but common).
3. Use ORDER BY to define the sequence for the calculation.
4. Use ROWS BETWEEN ... to specify the exact window size.

SELECT
  city,
  date,
  temperature,
  ROUND(
    AVG(temperature) OVER (
      PARTITION BY city
      ORDER BY date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 1
  ) AS mov_avg_3d_city
FROM weather;

SELECT
  sale_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM
  daily_sales;

Conclusion

You've now added two of the most common analytical patterns to your SQL toolkit. By mastering the frame clause, you can move beyond simple rankings to perform powerful trend analysis directly in the database.

Key Takeaways:

• Running Total: A cumulative calculation from the start of a partition. Use SUM(...) OVER (ORDER BY ...) which defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• Moving Average/Rolling Total: A calculation over a fixed-size, sliding window. You must define this window explicitly, for example: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-day window.
• The Frame Clause is Key: ROWS BETWEEN ... gives you precise control over the window of rows included in your calculation.
• Combine with PARTITION BY: You can calculate these metrics independently for different categories (products, regions, etc.) by adding a PARTITION BY clause, just as you did for ranking.

Next Lesson Preview:

So far, our trend analysis has relied on data that is already nicely structured with one row per time period (e.g., one row per day). But what if your raw data contains multiple transactions per day? In the next lesson, we will focus on time-based aggregations. You'll learn how to use date functions to group raw transactional data into daily, weekly, or monthly summaries, which is the foundational step for creating the time-series metrics we've analyzed today.