2

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.

  • @ypercubeᵀᴹ I am using current_balance as the default value in my first LAG function call. – Shaun Scovil Mar 17 '16 at 17:08
  • Oh, ok, I just noticed the insert .. values ...; – ypercubeᵀᴹ Mar 17 '16 at 17:14
2

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 ;
  • It should be b.current_balance + t.amount - SUM(t.amount) OVER ... when working in DESC order, but yes this is the correct solution. – Shaun Scovil Mar 17 '16 at 17:26
  • 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) – ypercubeᵀᴹ Mar 17 '16 at 17:30
5

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
  • 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 Mar 17 '16 at 16:16
  • 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? – Shaun Scovil Mar 17 '16 at 17:03
  • You should edit the question. It should also work in the other direction. I can't look at it right now. – Julien Vavasseur Mar 17 '16 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.