14 / 44

Running total per account

MediumP1Coding

Table transactions:

Column	Type
id	int (primary key)
account_id	int
txn_date	date
amount	int (positive for deposits, negative for withdrawals)

Write a query that returns every transaction with the running total of its account: the sum of amount for all of that account's transactions up to and including this one, ordered by txn_date and then by id.

Return the columns id, account_id, txn_date, amount and running_total (an integer), ordered by account_id, txn_date, id.

Schema

CREATE TABLE transactions (
  id int PRIMARY KEY,
  account_id int NOT NULL,
  txn_date date NOT NULL,
  amount int NOT NULL
);

Example 1

Input: transactions: (1, 7, '2024-01-01', 100), (2, 7, '2024-01-02', -30), (3, 8, '2024-01-01', 50)
Output: 1, 7, 2024-01-01, 100, 100 2, 7, 2024-01-02, -30, 70 3, 8, 2024-01-01, 50, 50

Constraints

PostgreSQL dialect.
Several transactions can share a date; id breaks the tie.
Each account has its own total.

Hints

PostgreSQL

Run checks the 2 visible tests, Submit all 6. ⌘Enter runs, ⌘⇧Enter submits.