For account 101, return each transaction's date, amount, and a running cumulative total (runningtotal) ordered by…
SQLLevel: IntermediateWindow functions
For account 101, return each transaction's date, amount, and a running cumulative total (running_total) ordered by date.
Table definitions (from the source exercise):
CREATE TABLE transactions(transaction_id INTEGER PRIMARY KEY, account_id INTEGER, transaction_date TEXT, amount REAL, merchant_category TEXT);
Hints
- Write an interleaving of two concurrent operations.
- Which observations does the isolation level permit, and what happens on a mid-operation failure?
Answer
Approach: SUM(amount) OVER (ORDER BY transaction_date) after filtering to the account.
SELECT transaction_date, amount,
SUM(amount) OVER (ORDER BY transaction_date, transaction_id) AS running_total
FROM transactions
WHERE account_id = 101
ORDER BY transaction_date;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT