Running totals & moving averages
Overview
Window frames let you narrow down the window of rows for each calculation. You define the frame boundaries using keywords like `UNBOUNDED PRECEDING` (start of partition), `N PRECEDING` (N rows back), `CURRENT ROW`, and `N FOLLOWING`. By adding a clause like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, you can compute running totals (cumulative sums), or use ROWS BETWEEN 1 PRECEDING AND CURRENT ROW for moving averages over an ordered set.
They allow for calculating metrics that accumulate over time (e.g., total sales up to the current day) or smooth out fluctuations (e.g., a 7-day moving average).
Where used: revenue growth tracking, stock price moving averages
Why learn this
- Generate cumulative metrics like 'Total Sales YTD' dynamically.
- Smooth out noisy data using moving averages.
Query walkthrough
SELECT id, order_date, COUNT(id) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_count FROM orders WHERE customer_id IN (1, 2) ORDER BY order_date;Focus: Watch how the running_count increases with each row as the window expands to include the current row.
Common mistakes
- Default frame with ORDER BY: If you use an ORDER BY in your OVER clause but don't specify a frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE groups tied values together and evaluates them as a single block, whereas ROWS strictly evaluates row-by-row. This can cause unexpected behavior with tied values if you meant to use ROWS.
Glossary
- cumulative
- Increasing or growing by adding things together over time, like a running total of sales.
- accumulate
- To gather or collect a growing amount of something over time.
- fluctuations
- Frequent and sometimes unpredictable changes or variations in data.
Recall questions
- What frame definition is used to calculate a cumulative sum from the first row to the current row?
Understanding checks
What rows does this query return?
1 | 2023-02-01 | 1 2 | 2023-03-15 | 3
For the first row (id=1), there is no preceding row, so the sum is 1. For the second row (id=2), it adds the current id (2) and the preceding id (1) to get 3.
What rows does this query return?
Yara | 90000 | NULL Xavier | 95000 | 90000 Wendy | 130000 | 95000
The frame looks at all rows before the current one, but excludes the current row. Yara has no preceding rows, Xavier sees Yara, Wendy sees Yara and Xavier.
Practice tasks
Calculate a cumulative sum of salary
Given this query, add a running total of the salary column, ordered by salary ascending.
Challenge
Calculate a 2-row moving average of salary
Write a query returning name, salary, and a moving average of salary that includes the current row and the 1 preceding row. Order by salary ascending. Filter for the Engineering department.
Continue learning
Previous: OVER & PARTITION BY
Previous: LEAD & LAG