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);


SQL

WITH transfers AS (
  SELECT *
  FROM temp.transfers
  WHERE account_id = 1
    AND activity_date BETWEEN '2015-10-01'::DATE AND CURRENT_TIMESTAMP
  ORDER BY activity_date DESC
)
SELECT
  t.activity_date
, t.amount
, (LAG(balance, 1, b.current_balance) OVER (ORDER BY t.activity_date DESC)::INT) - (LAG(t.amount, 1, 0) OVER (ORDER BY t.activity_date DESC)::INT) AS balance
FROM transfers t
JOIN temp.balances b ON b.account_id = t.account_id


This query fails because I am trying to access the value of balance that is being generated by the window function. I looked into WITH RECURSIVE, which seems like the right technique to use, but couldn't get it to work. I'm also not sure if it can be used in conjunction with other WITH clauses, which I would need to do.

So my question is: How can I calculate the balance after each transfer, working backward from the current balance, in PostgreSQL 9.3?

Update

Using a slightly-modified version of Julien's answer below, I tried this:

WITH transfers AS (
  SELECT *
  FROM temp.transfers
  WHERE account_id = 1
    AND activity_date BETWEEN '2015-10-01'::DATE AND CURRENT_TIMESTAMP
)
SELECT
  t.activity_date
, t.amount
, b.current_balance - SUM(t.amount) OVER(ORDER BY t.activity_date DESC) AS balance
FROM transfers t
JOIN temp.balances b ON b.account_id = t.account_id


...which is almost correct, except that the balances are shifted up one row.
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