Cumulative Sums With Window Frames
Building a running total using SUM OVER with an ordered frame.
The Running Total Question
Almost every analyst interview includes some form of: "Show me the cumulative revenue over time." A running total is a sum that grows row by row, accumulating everything from the start up to the current row.
Before window functions existed, candidates solved this with a slow self-join or a correlated subquery. The modern, expected answer is SUM(...) OVER (ORDER BY ...). Knowing the window-frame version signals you understand SQL written after about 2012.
Anatomy of an Ordered Window Sum
A running total is just an aggregate turned into a window function. You keep SUM(amount) but add an OVER clause with an ORDER BY.
The ORDER BY inside OVER is what makes it cumulative: it tells SQL to accumulate rows in that sequence. Without an ORDER BY, SUM would total the whole partition for every row instead of growing.
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;All lessons in this course
- Cumulative Sums With Window Frames
- ROWS vs RANGE Framing
- Moving Averages Over a Sliding Window
- Cumulative Distribution and Percent of Total