How can I calculate a running total with a window function?

Assuming your table is named expenses and has expense_id, expense_date, and amount columns, use SUM as a window function:

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

The OVER clause turns the aggregate into a window calculation. Its ORDER BY sets the sequence used to decide which expenses come before each row; the explicit frame includes rows from the start of that sequence through the current row. [S1] Here, expense_id is a tie-breaker so expenses on the same date have a consistent sequence, assuming it uniquely distinguishes rows.

Here is a made-up example, assuming these rows are in expenses:

expense_id | expense_date | amount
1          | 2025-04-02   | 12.00
2          | 2025-04-03   | 8.50
3          | 2025-04-03   | 4.00
4          | 2025-04-06   | 10.00

Using the query above, the result would be:

expense_id | expense_date | amount | running_total
1          | 2025-04-02   | 12.00  | 12.00
2          | 2025-04-03   | 8.50   | 20.50
3          | 2025-04-03   | 4.00   | 24.50
4          | 2025-04-06   | 10.00  | 34.50

The two expenses dated April 3 are accumulated in expense_id order because that is the tie-breaker specified in the window. That ordering is a choice for this example; if you have a meaningful within-day sequence, use that column instead.

The ORDER BY inside OVER defines the calculation sequence. The final ORDER BY after FROM controls the order in which rows are displayed. They serve different purposes, so keep the outer one when you want the results shown in date-and-ID order. [S1]

The explicit frame, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, starts at the first row in the ordered sequence and ends at the current row. That makes each result accumulate from the beginning through that row. [S1] The supplied SQLite guidance illustrates this frame; if you omit it or use another database, consult that database’s documentation rather than assuming identical behavior.

When dates repeat, add a stable tie-breaker—such as an expense ID—to the window’s ORDER BY. That defines which tied row comes first in the calculation. Use the same ordering in the outer ORDER BY if you want the displayed rows to follow that sequence. [S1]

To filter on the calculated total in SQLite, put the window calculation in a subquery and apply the condition outside it:

SELECT *
FROM (
  SELECT
    expense_id,
    expense_date,
    amount,
    SUM(amount) OVER (
      ORDER BY expense_date, expense_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
  FROM expenses
) AS totals
WHERE running_total >= 25
ORDER BY expense_date, expense_id;

SQLite permits window functions in the result set and ORDER BY; filtering their results is done in an outer query. [S1] Adjust the threshold and column names to match your data.