PostgreSQL: Calculate balance working backwards from current balance

I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, withdrawals are negative).

I need to create an account summary that contains each transfer for an account, as well as the balance resulting from that transfer.

Example:

+--------------------------+--------+---------+
|      activity_date       | amount | balance |
+--------------------------+--------+---------+
| 2015-12-24T20:27:00.670Z |     10 |     180 |
| 2015-12-19T12:13:50.085Z |   -275 |     170 |
| 2015-12-18T23:56:22.513Z |     10 |     445 |
| 2015-12-18T23:54:46.880Z |     50 |     435 |
| 2015-12-17T03:32:10.707Z |   -120 |     385 |
| 2015-12-12T03:56:50.775Z |     35 |     505 |
| 2015-12-11T23:09:40.211Z |    -20 |     470 |
| 2015-12-03T01:17:59.460Z |    -10 |     490 |
| 2015-11-23T15:39:35.003Z |    500 |     500 |
+--------------------------+--------+---------+

Since I only have the current balance for the account, I need to start there and work my way backward to a given date. In a spreadsheet, I would calculate the new balance by subtracting the amount of the transfer from the previous balance, but I'm having trouble translating this into SQL.

Schema

```
CREATE SCHEMA temp;

CREATE TABLE temp.balances (
account_id INT
, current_balance INT
);

CREATE TABLE temp.transfers (
account_id INT
, activity_date TIMESTAMP
, amount INT
);

INSERT INTO temp.balances (account_id, current_balance)
VALUES (1, 180);

INSERT INTO temp.transfers (account_id, activity_date, amount)
VALUES (1, '2015-12-24T20:27:00.670Z', 10)
, (1, '2015-12-19T12:13:50.085Z', -275)
, (1, '2015-12-18T23:56:22.513Z', 10)
, (1, '2015-12-18T23:54:46.880Z', 50)
, (1, '2015-12-17T03:32:10.707Z', -120)
, (1, '2015-12-12T03:56:50.775Z', 35)
, (1, '2015-12-11T23:09:40.211Z', -20)
, (1, '2015-12-03T01:17:59.460Z', -10)
, (1, '2015-11-23T15:39:35.003Z', 500)

You need a Window Function indeed. However LAG is not the right one. SUM(...) OVER(...) is the one you want. See SQL Fiddle.

Query:

SELECT account_id, activity_date, amount
    , SUM(amount) OVER(PARTITION BY account_id ORDER BY activity_date) as balance
FROM transfers t
ORDER BY account_id, activity_date DESC;

Output:

account_id  | activity_date                 | amount    | balance
1           | 2015-12-24 20:27:00.6700000   | 10        | 180
1           | 2015-12-19 12:13:50.0850000   | -275      | 170
1           | 2015-12-18 23:56:22.5130000   | 10        | 445
1           | 2015-12-18 23:54:46.8800000   | 50        | 435
1           | 2015-12-17 03:32:10.7070000   | -120      | 385
1           | 2015-12-12 03:56:50.7750000   | 35        | 505
1           | 2015-12-11 23:09:40.2110000   | -20       | 470
1           | 2015-12-03 01:17:59.4600000   | -10       | 490
1           | 2015-11-23 15:39:35.0030000   | 500       | 500

StackExchange Database Administrators Q#132551, answer score: 7