How do you calculate a running total or moving average?

Q: How do you calculate a running total or moving average?

**Target Audience:** Beginners who need a cumulative sum or a smoothed trend line in SQL, plus candidates who want to explain frames (`ROWS`, `RANGE`, `GROUPS`) precisely. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Version facts are cited. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A fitness tracker shows two numbers under each day. One is "steps this month so far": Monday 8,000, Tuesday 15,000, Wednesday 21,000, always adding today to everything before. The other is the "7-day average" line on the chart: it ignores the one lazy Sunday and the one marathon Saturday and shows the

The problem

Question presented to candidate: "Given a table of daily sales per store, how would you add a running total, and a 3-day moving average, to every row? What exactly decides which rows go into each calculation?"

What a strong answer should cover:

A running total is SUM(amount) OVER (PARTITION BY store ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW); a moving average is AVG(...) with a bounded frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
The frame clause decides the rows: ROWS counts physical rows, RANGE uses value distance on the ORDER BY column (peers included), GROUPS counts distinct peer groups
The default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so rows tied on the order key share one running value; use ROWS plus a unique tiebreaker for a strict row-by-row total
Missing dates: "last 3 rows" is not "last 3 days"; use RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW (PostgreSQL 11+) or a numeric day key, or fill the gaps first
The first rows of a moving average have short frames (fewer than 3 rows); count the frame and blank them if only full windows are wanted

Clarifying questions expected:

Should the total restart per store (or per month), or run across everything?
Are there missing days, and should "3-day" mean three calendar days or three rows?
Can several rows share the same date, and if so, should they be summed together?

Code / implementation expected: Yes — a running total with PARTITION BY and a moving average with an explicit ROWS frame.

Schema used by the examples:

window-functions
analytics

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who need a cumulative sum or a smoothed trend line in SQL, plus candidates who want to explain frames (ROWS, RANGE, GROUPS) precisely. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Version facts are cited.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A fitness tracker shows two numbers under each day. One is "steps this month so far": Monday 8,000, Tuesday 15,000, Wednesday 21,000, always adding today to everything before. The other is the "7-day ave

The solution is hidden so you can try first

14 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLRunning total per store, next to the store total

SQL3-row moving average for store A, with the number of rows in each frame

SQLThe default frame (RANGE) vs ROWS when two orders share a day

PostgreSQL: last 3 rows vs last 3 calendar days when a day is missing

SELECT day::date AS day, amount,
       SUM(amount) OVER (ORDER BY day::date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS last_3_rows,
       SUM(amount) OVER (ORDER BY day::date
                         RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW) AS last_3_days
FROM daily_sales
WHERE store = 'A'
ORDER BY day;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.