Calculating a cumulative sum with an alias in postgresql I have two tables - user credit and payments.
credit is just a total of the amount of credit a user has at the present.
Payments is a list of payments by date that a user needs to make.
I need to create a report showing the amount of credit available for each payment a user is to make - taking into account the credit used up on previous payments.
CREATE TABLE credit (
  user_id INT,
  amount INT
);

CREATE TABLE payments (
  user_id INT,
  due timestamp,
  amount INT
);

INSERT INTO credit values (1, 100);
INSERT INTO credit values (2, 200);

INSERT INTO payments values (1, '2021-04-01', 20);
INSERT INTO payments values (1, '2021-04-02', 20);
INSERT INTO payments values (1, '2021-04-03', 20);
INSERT INTO payments values (2, '2021-04-01', 100);
INSERT INTO payments values (2, '2021-04-02', 300);
INSERT INTO payments values (3, '2021-04-03', 20);

Result should look like this:




user_id
due
amount
credit_available
credit_used
credit_remaining




1
2021-04-01
20
100
20
80


1
2021-04-02
20
80
20
60


1
2021-04-02
20
60
20
40


2
2021-04-01
100
200
100
100


2
2021-04-01
200
100
100
0


3
2021-04-01
20
0
0
0




I have set up a fiddle here:
http://sqlfiddle.com/#!17/e812b/9/0
I thought I could do it pretty simply with a LAG() but I can't reference the previous row's calculated alias credit_remaining column within the select like so:
SELECT
    ...,
    least(0, payments.amount - LAG(credit_remaining, 1, credit.amount) OVER (PARTITION BY user_id ORDER BY user_id, due)) as credit_remaining
FROM ...
SELECT user_id, 
       payments.due, 
       payments.amount, 
       COALESCE(credit.amount, 0) + payments.amount - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due) available,
       LEAST(payments.amount, COALESCE(credit.amount, 0) + payments.amount - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due)) used,
       GREATEST(0, COALESCE(credit.amount, 0) - SUM(payments.amount) OVER (PARTITION BY user_id ORDER BY payments.due)) remaining
FROM ( SELECT user_id FROM credit
       UNION 
       SELECT user_id FROM payments ) userlist
LEFT JOIN credit USING (user_id)
LEFT JOIN payments USING (user_id)
ORDER BY user_id, due

https://dbfiddle.uk/?rdbms=postgres_9.6&amp;fiddle=7e5814dbe02ced69684d331b277dd205
PS. payments (user_id, due) must be defined as unique - if not then the output is indefinite (or you must use another ordering in the window definition, for example, by payments.id additionally).