PostgreSQL: Calculate balance working backwards from current balance

Question

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月24日T20:27:00.670Z | 10 | 180 |
| 2015年12月19日T12:13:50.085Z | -275 | 170 |
| 2015年12月18日T23:56:22.513Z | 10 | 445 |
| 2015年12月18日T23:54:46.880Z | 50 | 435 |
| 2015年12月17日T03:32:10.707Z | -120 | 385 |
| 2015年12月12日T03:56:50.775Z | 35 | 505 |
| 2015年12月11日T23:09:40.211Z | -20 | 470 |
| 2015年12月03日T01:17:59.460Z | -10 | 490 |
| 2015年11月23日T15: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.

• Shaun Scovil: @ypercubeTM I am using current_balance as the default value in my first LAG function call.
• ypercubeᵀᴹ: Oh, ok, I just noticed the insert .. values ...;

Answer 1

You can modify Julien's query to use (as a starting point) the current_balance from the other table:

SELECT t.account_id, t.activity_date, t.amount,
 b.current_balance - COALESCE(SUM(t.amount) OVER w, 0) AS balance
ORDER BY account_id, activity_date DESC ;
FROM temp.balances AS b
 JOIN temp.transfers AS t
 ON t.account_id = b.account_id
WINDOW w AS (PARTITION BY t.account_id 
 ORDER BY t.activity_date DESC
 ROWS BETWEEN UNBOUNDED PRECEDING 
 AND 1 PRECEDING) 
ORDER BY account_id , activity_date DESC ;

• Shaun Scovil: It should be b.current_balance + t.amount - SUM(t.amount) OVER ... when working in DESC order, but yes this is the correct solution.
• ypercubeᵀᴹ: Yes, I had that right but somehow mixed it up in my head and reversed before posting. I edited with a slightly less complicated way (without using the current amount in every row calculation)

Answer 2

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

• Shaun Scovil: I started out with this, but I was using ORDER BY activity_date DESC in the OVER clause, which was mucking things up. Thank you!
• Shaun Scovil: Actually, this isn't correct. The reason I am using DESC is because I need to be able to query a date range that does not necessarily include the first transfer (the initial deposit). So I need to work backwards from the current balance, but this solution works forward from the first transfer. Any thoughts?
• Julien Vavasseur: You should edit the question. It should also work in the other direction. I can't look at it right now.