0Pricing
SQL Academy · Lesson

Running Totals and Moving Averages

Compute running sums and moving averages with SUM(...) OVER (ORDER BY ... ROWS BETWEEN ...).

Running Totals and Moving Averages is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Running Total Basics

A running total is a SUM with ORDER BY in the window — and no frame clause, so the default frame is "from the start to the current row":

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
ORDER BY day;

Per-Group Running Total

Reset the running total per user:

SELECT id, user_id, total,
       SUM(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS user_running_total
FROM orders;

Moving Average

An N-day rolling average uses a ROWS frame:

SELECT day, revenue,
       AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7d
FROM daily_revenue;

Centred Moving Average

Centred 7-day window (3 before, current, 3 after):

SELECT day, revenue,
       AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
       ) AS avg_7d_centred
FROM daily_revenue;

Cumulative Distribution

What fraction of total revenue have we reached by each day?

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day)
         / SUM(revenue) OVER ()  AS cum_share
FROM daily_revenue;

ROWS vs RANGE

  • ROWS — count rows physically (N rows before/after)
  • RANGE — span values logically (e.g. 7 days based on date value)
-- ROWS: 6 previous rows + this one (may skip dates with no data)
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- RANGE: include any row with a day within '6 days' of the current row
AVG(revenue) OVER (ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

Cumulative Maximum

Best-so-far at each point:

SELECT day, total_users,
       MAX(total_users) OVER (ORDER BY day) AS peak_so_far
FROM daily_metrics;

Cumulative Count

Total events seen up to this row:

SELECT id, ts,
       COUNT(*) OVER (ORDER BY ts) AS events_so_far
FROM events;

Spotting Anomalies

Compare today's revenue to the rolling baseline:

SELECT day, revenue, avg_7d,
  CASE WHEN revenue < 0.5 * avg_7d THEN 'WARNING' ELSE 'OK' END AS status
FROM (
  SELECT day, revenue,
         AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_7d
  FROM daily_revenue
) sub;

Initial NULLs

With a 7-row window, the first 6 rows have NULL/partial averages. Decide whether to suppress them or treat partial windows as valid.

Performance

Running aggregates need partition-ordered input. An index on the ORDER BY column avoids the sort.

Recap

Frames unlock running calculations.

  • Default frame with ORDER BY = "start to current row"
  • ROWS BETWEEN n PRECEDING AND CURRENT ROW = rolling window
  • SUM/AVG/MAX over windows = cumulative or rolling

Quick Check

Which frame clause gives a 7-day rolling average up to the current row?

Frequently asked questions

Is the “Running Totals and Moving Averages” lesson free?

Yes — the full text of “Running Totals and Moving Averages” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Running Totals and Moving Averages”?

Compute running sums and moving averages with SUM(...) OVER (ORDER BY ... ROWS BETWEEN ...). You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Running Totals and Moving Averages” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. OVER, PARTITION BY, ORDER BY
  2. ROW_NUMBER, RANK, DENSE_RANK
  3. LAG, LEAD and Time-Series Patterns
  4. Running Totals and Moving Averages
← Back to SQL Academy