Calculate the cumulative transaction amount for each month using a transaction table.

Do it in two steps: collapse transactions to one row per month, then run a windowed running total over those monthly rows.

WITH monthly AS (
  SELECT DATE_TRUNC('month', txn_date) AS month,
         SUM(amount)                   AS monthly_amount
  FROM   transactions
  GROUP  BY 1
)
SELECT month,
       monthly_amount,
       SUM(monthly_amount) OVER (
         ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_amount
FROM   monthly
ORDER  BY month;

Why aggregate first

Running the window directly over raw transactions returns a cumulative value per transaction, not per month. Collapsing to monthly grain first is what makes the output one row per month.

The frame clause is not optional detail

Omitting ROWS BETWEEN gives you the SQL default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE is value-based: every row with the same ORDER BY value is folded into the same frame. Here the CTE guarantees one row per month, so both produce identical results. But if duplicate months ever reach that window, RANGE returns the same total for each of them while ROWS increments row by row. Being explicit costs nothing and makes intent unambiguous.

Per-customer running totals

Add a partition, and the total restarts for each customer:

SUM(monthly_amount) OVER (PARTITION BY customer_id ORDER BY month
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Months with no transactions

A cumulative series usually needs to be continuous. Months with zero transactions simply do not exist in transactions, so the running total skips them and any chart drawn from it is misleading. Generate a calendar spine and LEFT JOIN the aggregate onto it, using COALESCE(monthly_amount, 0) before the window so the total carries forward flat across empty months.

Note that DATE_TRUNC is PostgreSQL, Spark and Snowflake syntax; BigQuery uses DATE_TRUNC(txn_date, MONTH) with the arguments reversed.

In the interview, also mention that a cumulative total is a single pass over sorted data, which is why it scales far better than a self join on month <= month.