How do you compute a running total (cumulative sum) using a window function, and what frame clause does it use by default?

How to think about it

A running total is the gateway window function — interviewers use it to check that you understand the frame clause, not just the syntax. The follow-up is always about what happens when two rows share the same ORDER BY value, so address that proactively.

The basic pattern

SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

The explicit ROWS frame processes each physical row exactly once, giving you the classic staircase: 100, 150, 210, …

Partitioned — reset per customer

Add PARTITION BY to restart the running total for each group:

SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

Live example — with and without the frame clause

The frame options at a glance

Frame keyword	Meaning
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Every row from partition start up to this physical row
ROWS BETWEEN N PRECEDING AND CURRENT ROW	The current row plus N rows before it (moving window)
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	Every row up to and including all peers on the ORDER BY value

Moving average (7-day look-back)

SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

6 PRECEDING gives a window of 7 rows total (6 before + current). On early rows where fewer than 7 rows exist, the window simply uses however many rows are available.