All 40 lessons

Lesson 29 of 40 · Window Functions

Running Totals, Moving Averages and Window Frames

By Skillancy Editorial Team

Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.

A running total adds each row's value to the sum of all rows before it; a moving average averages a fixed number of recent rows. Both are window functions with a frame: a rule for which rows around the current row are included.

Syntax

SUM(col) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN frame_start AND frame_end
)
Frame boundaryMeaning
UNBOUNDED PRECEDINGthe first row of the window
n PRECEDINGn rows before the current row
CURRENT ROWthe current row
n FOLLOWINGn rows after the current row
UNBOUNDED FOLLOWINGthe last row of the window

When you write ORDER BY without a frame, the default is "from the first row up to the current row" (with ties included together), which is exactly a running total.

Setup: monthly revenue

Most examples use delivered revenue per month. We'll repeat this CTE in each query:

WITH monthly AS (
  SELECT DATE_TRUNC('month', o.order_date)::date AS month,
         ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 0) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY 1
)
SELECT * FROM monthly ORDER BY month;
Try it in the SQL Compiler

Result:

monthrevenue
2025-01-0132271
2025-02-0128839
2025-03-013492
2025-04-012988
2025-05-013685
2025-06-0110746
2025-08-016091
2025-09-013997
2025-10-0124995
2025-11-011297
2025-12-013397

(11 rows)

Examples

1. Running total

WITH monthly AS (
  SELECT DATE_TRUNC('month', o.order_date)::date AS month,
         ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 0) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY 1
)
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly
ORDER BY month
LIMIT 6;
Try it in the SQL Compiler

Result:

monthrevenuerunning_total
2025-01-013227132271
2025-02-012883961110
2025-03-01349264602
2025-04-01298867590
2025-05-01368571275
2025-06-011074682021

(6 rows)

2. Running total per group

Restart the cumulative spend for each customer with PARTITION BY:

WITH order_value AS (
  SELECT o.customer_id, o.order_id, o.order_date,
         SUM(oi.quantity * oi.unit_price) AS order_amount
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY o.customer_id, o.order_id, o.order_date
)
SELECT customer_id,
       order_date,
       order_amount,
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS lifetime_spend
FROM order_value
WHERE customer_id IN (1, 4)
ORDER BY customer_id, order_date;
Try it in the SQL Compiler

Result:

customer_idorder_dateorder_amountlifetime_spend
12025-01-0526597.0026597.00
12025-02-202499.0029096.00
12025-06-303597.0032693.00
12025-11-151297.0033990.00
42025-03-113791.003791.00
42025-08-022297.006088.00
42026-01-262196.008284.00

(7 rows)

3. Cumulative percentage (Pareto analysis)

Which products make up most of the revenue? Sort by revenue and accumulate the share:

WITH product_revenue AS (
  SELECT p.product_name,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)) AS revenue
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.product_name
)
SELECT product_name,
       ROUND(revenue, 0) AS revenue,
       ROUND(SUM(revenue) OVER (ORDER BY revenue DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
             * 100 / SUM(revenue) OVER (), 1) AS cumulative_pct
FROM product_revenue
ORDER BY revenue DESC
LIMIT 6;
Try it in the SQL Compiler

Result:

product_namerevenuecumulative_pct
Redmi Note 13 Pro6839734.8
Samsung 43" Smart TV5598363.3
Women's Silk Saree2422275.6
boAt Airdopes 141779479.6
Men's Cotton Kurta750183.4
Running Shoes674786.8

(6 rows)

Three products produce over 75% of revenue, a typical 80/20 pattern.

4. Moving average

A 3-month moving average smooths out spikes. The frame is the current row and the two before it:

WITH monthly AS (
  SELECT DATE_TRUNC('month', o.order_date)::date AS month,
         ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 0) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY 1
)
SELECT month,
       revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month
                                ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS moving_avg_3m
FROM monthly
ORDER BY month
LIMIT 6;
Try it in the SQL Compiler

Result:

monthrevenuemoving_avg_3m
2025-01-013227132271
2025-02-012883930555
2025-03-01349221534
2025-04-01298811773
2025-05-0136853388
2025-06-01107465806

(6 rows)

The first two months average fewer than 3 values because there aren't enough earlier rows.

5. LAST_VALUE and full-window frames

LAST_VALUE with the default frame just returns the current row. Extend the frame to the whole partition:

SELECT customer_id,
       order_date,
       LAST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS default_frame,
       LAST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_order
FROM orders
WHERE customer_id = 1
ORDER BY order_date;
Try it in the SQL Compiler

Result:

customer_idorder_datedefault_framelatest_order
12025-01-052025-01-052025-11-15
12025-02-202025-02-202025-11-15
12025-06-302025-06-302025-11-15
12025-11-152025-11-152025-11-15

(4 rows)

Common mistakes

  • Ties in the ORDER BY. With the default (RANGE) frame, rows with the same date are added together. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a strictly row-by-row total.
  • Missing months. A "3-month" average over rows skips gaps. Fill missing months first.
  • Running totals on unaggregated rows. Aggregate to the reporting grain (day, month) in a CTE first.

Practice question

Show each order's date and a running count of delivered orders over time.

Show solution
SELECT order_id,
       order_date,
       COUNT(*) OVER (ORDER BY order_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS delivered_to_date
FROM orders
WHERE status = 'Delivered'
ORDER BY order_date
LIMIT 6;
Try it in the SQL Compiler

Result:

order_idorder_datedelivered_to_date
10012025-01-051
10022025-01-182
10032025-02-023
10052025-03-114
10072025-04-155
10082025-05-036

(6 rows)

Key takeaways

  • Running total: SUM(x) OVER (ORDER BY date); add PARTITION BY to restart per group.
  • Frames (ROWS BETWEEN ... AND ...) decide which rows are included.
  • Moving average: AVG(x) OVER (ORDER BY d ROWS BETWEEN n PRECEDING AND CURRENT ROW).
  • LAST_VALUE needs UNBOUNDED FOLLOWING to see the whole window.
Want every variation on one page?Window functions in SQL: the complete guide

Frequently asked questions

How do I calculate a running total in SQL?

Use SUM(value) OVER (ORDER BY date). Add PARTITION BY to restart the total for each group, for example each customer.

What is the difference between ROWS and RANGE?

ROWS counts physical rows. RANGE groups rows with the same ORDER BY value together, so tied dates are added at once. The default frame with ORDER BY is RANGE, which is why ties can make running totals jump.

Why does LAST_VALUE return the current row's value?

The default frame ends at the current row, so the 'last' value is the current one. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to look at the whole window.

← PreviousSQL LAG and LEADNext →SQL CREATE TABLE and Data Types
Newsletter

Keep learning SQL

One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.

Prefer a one-page summary? Download the free SQL cheat sheet (PDF)