I have two tables - user
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:
I have set up a fiddle here:
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 ...