Skip to content
>_sqlbuddy
mediumWindow FunctionsAggregationDate Functions

Running Total

Compute a running total of sales over time, partitioned per account.

Start practice

Problem statement

Prompt

Given the sales table below, write a query that returns each sale with a running_total column showing the cumulative sum of that account's sales up to and including the current sale, ordered by sale_date.

  • The running total must reset per account (each account's totals are computed independently).
  • Output columns: id, account_id, sale_date, amount, running_total.
  • Order the result by account_id, then sale_date, then id — sales sharing a date must have a deterministic order (use id as the tiebreaker).
  • sales.amount may be NULL; SUM ignores NULL values.

Example

idaccount_idsale_dateamount
112024-01-01100
212024-01-0550
322024-01-0230

Expected result (first two rows shown):

idaccount_idsale_dateamountrunning_total
112024-01-01100100
212024-01-0550150
322024-01-023030

Constraints

  • sales.id is the primary key.
  • sales.amount is nullable.

Tips

  • SUM(amount) OVER (PARTITION BY account_id ORDER BY sale_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
  • Omitting PARTITION BY gives a global running total — a common interview trap.

Schema

CREATE TABLE sales (
    id         INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    sale_date  DATE NOT NULL,
    amount     INTEGER  -- NULL allowed
);

Sample data

INSERT INTO sales (id, account_id, sale_date, amount) VALUES
  (1, 1, '2024-01-01', 100),
  (2, 1, '2024-01-05', 50),
  (3, 1, '2024-01-10', 25),
  (4, 2, '2024-01-02', 30),
  (5, 2, '2024-01-03', 20),
  (6, 2, '2024-01-09', 40);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: per-account running total, deterministic order via id tiebreaker.
SELECT id, account_id, sale_date, amount,
       SUM(amount) OVER (
           PARTITION BY account_id
           ORDER BY sale_date, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM sales
ORDER BY account_id, sale_date, id;

Key concepts

  • SUM OVER
  • ROWS BETWEEN
  • PARTITION BY
  • Tiebreakers

Related questions

Learn more