Running Total

Technical Explanation

A running total (also called cumulative sum) is a sequence of partial sums that accumulate values over rows, ordered by some criteria. It's useful for tracking growth, progress, or cumulative metrics over time.

How It Works

SUM(column) OVER (
    PARTITION BY ...
    ORDER BY ...
)

The ORDER BY within the window function determines the sequence of accumulation.

---

Code Examples

Using the CatCafe dataset:

-- Table: orders
-- id, customer_id, cat_id, order_date, total_amount, status

-- Basic running total: Cumulative revenue over time
SELECT
    order_date,
    total_amount as daily_revenue,
    SUM(total_amount) OVER (
        ORDER BY order_date
    ) as running_revenue
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- Running total per customer
SELECT
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) as customer_running_total,
    COUNT(*) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) as customer_order_num
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_date;

-- Running total with percentage of grand total
WITH daily_revenue AS (
    SELECT
        order_date,
        SUM(total_amount) as daily_total
    FROM orders
    WHERE status = 'completed'
    GROUP BY order_date
)
SELECT
    order_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY order_date) as running_revenue,
    ROUND(
        100.0 * SUM(daily_total) OVER (ORDER BY order_date) /
        SUM(daily_total) OVER (),
        2
    ) as pct_of_total
FROM daily_revenue
ORDER BY order_date;

-- Running total vs moving average comparison
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) as running_total,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7_days
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- Finding "breakpoints" where running total crosses thresholds
WITH running AS (
    SELECT
        order_date,
        total_amount,
        SUM(total_amount) OVER (ORDER BY order_date) as running_total
    FROM (
        SELECT
            DATE_TRUNC('day', order_date) as order_date,
            SUM(total_amount) as total_amount
        FROM orders
        WHERE status = 'completed'
        GROUP BY DATE_TRUNC('day', order_date)
    ) daily
)
SELECT
    order_date,
    running_total,
    CASE
        WHEN running_total >= 10000 AND
             LAG(running_total) OVER (ORDER BY order_date) < 10000
        THEN 'Crossed $10K!'
        WHEN running_total >= 5000 AND
             LAG(running_total) OVER (ORDER BY order_date) < 5000
        THEN 'Crossed $5K!'
        ELSE NULL
    END as milestone
FROM running
ORDER BY order_date;

---

The Cat Analogy

Think of a cat treat jar:

You give treats throughout the day:

Morning: 3 treats  → Running total: 3
Noon:    2 treats  → Running total: 5
Afternoon: 4 treats → Running total: 9
Evening: 1 treat   → Running total: 10

The running total tracks cumulative treats given. At the end of the day, you know:

• Exactly 10 treats total
• The cat's treat consumption pattern
• When you hit certain thresholds ("no more after 10!")

---

Common Pitfalls

Without ORDER BY

-- Running total without ORDER BY:
SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER () as ambiguous_total
FROM orders;
-- This gives the SAME total for every row (no sequence)
-- Because there's no order, SQL just adds all values

Date Gaps

-- If you have gaps in dates, running total jumps
-- But may not show the intermediate missing days
SELECT
    order_date,
    SUM(total_amount) OVER (ORDER BY order_date) as running
FROM orders;
-- Days without orders are skipped!
-- For continuous running totals, you need a date calendar table

---

Exercises

Exercise 1

Write a query showing the running total of revenue per customer, ordered by order date.

Exercise 2

What does this query return?

SELECT
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date)
FROM orders;

Why might this be misleading?

Exercise 3

Write a query to find the date when the cumulative revenue first exceeded $5,000.

Exercise 4

Write a query comparing each day's revenue to the running total to that point (same day not included).

Exercise 5

How would you calculate a running total per customer per month?

---

Key Takeaways

• Running total = cumulative sum over ordered rows
• Requires ORDER BY in the OVER() clause
• PARTITION BY resets the running total for each group
• Useful for tracking progress toward goals
• Use LAG to detect threshold crossings
• Without ORDER BY, you just get the total sum repeated on every row