SQL Running Totals and Moving Averages with Window Functions

How the window grows

With ORDER BY inside OVER, the window for each row runs from the first row up to the current one, so SUM becomes a running total. Without ORDER BY, every row would see the whole partition and get the grand total.

A frame clause fixes the window size. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row and the two before it, a 3-row moving window. The first rows simply have fewer rows to average.

Ties in the sort column

The default frame is RANGE, which treats rows with the same ORDER BY value as one step, so tied rows show the same running total. Add a tie-breaker or use ROWS when each row should step on its own.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_total
FROM orders
ORDER BY customer_id, order_date;

SELECT order_date, amount,
       ROUND(AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3
FROM orders
ORDER BY order_date;

Common mistakes

• Missing ORDER BY in OVER. SUM(amount) OVER () gives the grand total on every row, not a running total.
• Ties with the default frame. Rows sharing a date get the same running total under RANGE. Add a unique tie-breaker such as an id.
• A self join instead. Summing earlier rows with a self join works but is slow and easy to get wrong. Window functions are the expected answer.

Practise Running Totals & Moving Averages

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   3-Day Moving Average of Orders

   Smooth daily order counts with a trailing three-day average.

   Medium✓ Solved
2. 2

   Accounts That Went Negative

   Use running balances to find accounts that ever overdrew and when it first happened.

   Medium✓ Solved
3. 3

   Products Driving 80% of Revenue

   Find the smallest set of top products whose cumulative revenue reaches 80% of the total.

   Medium✓ Solved
4. 4

   Last Passenger to Board

   Find the last person who can board a bus with a 1000 kg weight limit.

   Medium✓ Solved
5. 5

   User Session Reconstruction

   Group raw click events into sessions using a 30-minute inactivity rule.

   Hard✓ Solved
6. 6

   Three-Month Salary Window

   Each employee's rolling three-month salary total, excluding their most recent month.

   Hard✓ Solved

Want a full set? Work through the Window Functions study plan.

Questions people ask

What is the difference between ROWS and RANGE?

ROWS counts physical rows; RANGE groups rows with equal ORDER BY values. For a fixed-size moving window, use ROWS.

Can I compute a 7-day rolling sum?

If there is exactly one row per day, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW works. With gaps between days, fill in missing dates first, or join on a date range.

Related concepts

• SQL LAG and LEAD Functions Explained
• SQL ROW_NUMBER vs RANK vs DENSE_RANK