IntermediateLesson 17 of 306 min read

Window Functions: Running Totals, LAG and Moving Averages

Build running totals, changes from the previous row, shares of the total and moving averages, and avoid the tie trap in the default frame.

In this lesson you will

  • Build a running total
  • Use LAG for changes
  • Calculate share of total and moving averages
  • Avoid the RANGE tie trap

About 30 minutes

Running totals, changes and moving averages

Maya’s monthly report needs more than ranks. She wants a running total of revenue as the orders arrive, the change from the previous order, each order’s share of the total and a smoothed average that evens out the ups and downs. Before window functions, each of these needed a clumsy self-join. Now each is one extra column. This lesson shows the four patterns that cover most reporting work, and the one trap that catches almost everybody.

The frame: which rows the window sees

A window function with ORDER BY looks at a frame, the set of rows around the current one. You can state it exactly. A ROWS frame from 2 PRECEDING to the current row means the current row and the two before it, and one from UNBOUNDED PRECEDING to the current row means everything from the start up to this row, which gives a running total. Without a frame, an ordered window uses a default one, and that default is the source of the trap described below. The pattern is LAG for the value one row back and LEAD for one row forward, which are the tools for ‘change since last time’.

Five window queries on shipped orders

-- 1
WITH order_totals AS (
  SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT order_id, order_date, total,
  SUM(total) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM order_totals WHERE status = 'shipped' ORDER BY order_date, order_id

-- 2
WITH order_totals AS (
  SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT order_id, total, total - LAG(total) OVER (ORDER BY order_date, order_id) AS change
FROM order_totals WHERE status = 'shipped' ORDER BY order_date, order_id

-- 3
WITH order_totals AS (
  SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT order_id, total, ROUND(100.0 * total / SUM(total) OVER (), 1) AS percent_of_total
FROM order_totals WHERE status = 'shipped' ORDER BY order_date, order_id

-- 4
WITH order_totals AS (
  SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT order_id, ROUND(AVG(total) OVER (ORDER BY order_date, order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_last_3
FROM order_totals WHERE status = 'shipped' ORDER BY order_date, order_id

-- 5
WITH order_totals AS (
  SELECT o.id AS order_id, o.customer_id, o.order_date, o.status, SUM(oi.qty * p.price) AS total
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  JOIN products AS p ON p.id = oi.product_id GROUP BY o.id)
SELECT order_id, total, SUM(total) OVER (ORDER BY total) AS default_frame,
  SUM(total) OVER (ORDER BY total, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_frame
FROM order_totals WHERE total BETWEEN 23 AND 30 ORDER BY total, order_id
-- 1
order_id | order_date | total | running
1 | 2026-06-03 | 42.00 | 42.00
2 | 2026-06-10 | 23.50 | 65.50
3 | 2026-07-01 | 33.00 | 98.50
5 | 2026-08-02 | 26.50 | 125.00
6 | 2026-08-20 | 27.50 | 152.50
8 | 2026-09-18 | 41.50 | 194.00
9 | 2026-10-02 | 63.00 | 257.00
-- 2
order_id | total | change
1 | 42.00 | NULL
2 | 23.50 | -18.50
3 | 33.00 | 9.50
5 | 26.50 | -6.50
6 | 27.50 | 1.00
8 | 41.50 | 14.00
9 | 63.00 | 21.50
-- 3
order_id | total | percent_of_total
1 | 42.00 | 16.30
2 | 23.50 | 9.10
3 | 33.00 | 12.80
5 | 26.50 | 10.30
6 | 27.50 | 10.70
8 | 41.50 | 16.10
9 | 63.00 | 24.50
-- 4
order_id | avg_last_3
1 | 42.00
2 | 32.75
3 | 32.83
5 | 27.67
6 | 29.00
8 | 31.83
9 | 44.00
-- 5
order_id | total | default_frame | row_frame
2 | 23.50 | 23.50 | 23.50
7 | 24.00 | 71.50 | 47.50
10 | 24.00 | 71.50 | 71.50
5 | 26.50 | 98.00 | 98.00
6 | 27.50 | 125.50 | 125.50

Query 1 builds a running total over the seven shipped orders, in date order. It ends at 257.00, the same as the shipped revenue found in lesson 10, which is a satisfying cross-check. Query 2 uses LAG to subtract the previous order’s total. The first order has nothing before it, so its change is NULL. Orders that fell below the one before them show a negative change: order 2 by 18.50, order 5 by 6.50. Query 3 divides each total by the grand total, using SUM with an empty OVER bracket, which means the window is the whole result. The biggest order is 9 at 24.5 per cent. Because each share is rounded, the shares add up to 99.8, not exactly 100, which is a normal rounding effect. Query 4 averages each order with the two before it. The first order has no predecessors, so it is averaged alone, and the seven averages range from 27.67 to 44.00.

The tie trap: RANGE versus ROWS

Query 5 shows the trap. It uses the orders with totals between 23 and 30. Two of them, orders 7 and 10, have the same total of 24.00. The first running total uses the default frame, which, in standard SQL and in most databases, is a RANGE frame. A RANGE frame treats rows with equal sort values as peers, and includes all of them. Both tied orders therefore show the same running total, 71.50, because each includes the other. The second column uses an explicit ROWS frame and a tie-breaker, and moves one row at a time: 47.50 for the first tied order and 71.50 for the second. A running total that gives two rows the same value looks odd and can be wrong for your purpose. The cure is to say ROWS, and to add a unique column to the ORDER BY so that the order is fully decided.

Habits for window reports

  • State the frame with ROWS whenever you want a running or moving calculation.
  • Add a unique tie-breaker to every ORDER BY inside OVER.
  • Cross-check a running total against a plain SUM.
  • Remember that LAG gives NULL for the first row, and decide how to display it.
  • Filter the rows with WHERE first, and the window then sees only those rows. Filter on a window result in an outer query.
Common mistake: running totals over tied values

Trusting the default frame for a running total when the sort column has ties. Two rows can share a total, and a later total can jump. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Made-up sample data

The orders are made up, in sample money. Window frames are standard SQL, and supported in recent versions of the major databases, but the defaults and some frame options differ in places, so check your documentation.

Write a running count of customers by joining date, using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Check the last value against a plain COUNT.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does a running total need?

  1. A GROUP BY on id
  2. A subquery in WHERE
  3. SUM over a window ordered by date with a frame from the start to the current row
  4. A UNION

The frame from unbounded preceding to the current row accumulates.

What does LAG return?

  1. The value from an earlier row in the window
  2. The last row of the table
  3. The sum of earlier rows
  4. The next row

It was used to find the change from the previous order.

Why did the first order have a NULL change?

  1. Its total was zero
  2. It was cancelled
  3. There is no earlier row to look back at
  4. LAG failed

LAG returns NULL when no earlier row exists.

Why did tied orders share a running total with the default frame?

  1. A RANGE frame treats equal sort values as peers
  2. The orders were deleted
  3. SUM ignores ties
  4. The date was NULL

Both showed 71.50.

What is the cure for that trap?

  1. Remove the ORDER BY
  2. Use a ROWS frame and a unique tie-breaker
  3. Use RANK
  4. Add LIMIT

ROWS moves one row at a time.

Summary

  • A frame says which rows the window sees.
  • ROWS UNBOUNDED PRECEDING gives a running total.
  • LAG gives the previous row for changes.
  • Use ROWS and a tie-breaker to avoid shared totals on ties.

Next lesson

Lesson 18 shows how to create tables with constraints that keep data trustworthy.

Questions

What is the default frame?

With ORDER BY, standard SQL and most databases use a RANGE frame from the start to the current row. That includes tied rows, which is why explicit ROWS is safer.

Can a moving average use fewer rows at the start?

Yes. The first rows simply have fewer rows to average, as the lesson shows.

Do all databases offer LAG?

The major ones do in recent versions. Check your own.

Progress is saved in this browser.