SQL Intermediate Lesson 3: Running Totals and Moving Averages

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 3 of 10

Aggregate functions become running calculations when you add OVER with an ORDER BY.

WITH m AS (SELECT month, SUM(amount) AS total FROM sales GROUP BY month)
SELECT month, total,
       SUM(total) OVER (ORDER BY month)                                   AS running_total,
       AVG(total) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3,
       100.0 * total / SUM(total) OVER ()                                  AS pct_of_total
FROM m;

Common mistakes

• Running totals on unaggregated rows when you wanted monthly ones: aggregate in a CTE first.
• Moving average at the start covers fewer months (1, then 2). Filter those rows out if you need a full window.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.