Calculating a cumulative sum with an alias in postgresql

Question

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 ...

Akina · Accepted Answer · 2021-03-04 12:01:14Z

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&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).

@GuyBowden I'd prefer to use minimal amount of window definitions... — Akina
– Akina, Commented Mar 4, 2021 at 12:02

Guy Bowden · Accepted Answer · 2021-03-04 11:59:33Z

Another option (similar but independent to Akina - I just came back to answer it and saw his!)

Basically flipping the question a bit:

The amount of credit available can be worked out by summing the total previous payments subtracting the the total credit (and not allowing negative numbers):

SELECT payments.user_id, payments.due, payments.amount amount_due, greatest (0, coalesce(credit.amount, 0) - coalesce(sum(payments.amount) OVER (PARTITION BY payments.user_id ORDER BY payments.due ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)) credit_available, least (payments.amount, greatest (0, credit.amount - coalesce(sum(payments.amount) OVER (PARTITION BY payments.user_id ORDER BY payments.due ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0))) credit_used, greatest (0, coalesce(credit.amount, 0) - sum(payments.amount) OVER (PARTITION BY payments.user_id ORDER BY payments.due ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) credit_remaining FROM payments LEFT JOIN credit ON credit.user_id = payments.user_id ORDER BY payments.user_id, payments.due;

http://sqlfiddle.com/#!17/e812b/11

Stack Exchange Network

Calculating a cumulative sum with an alias in postgresql

2 Answers 2

Hot Network Questions

Calculating a cumulative sum with an alias in postgresql

2 Answers 2

Related

Hot Network Questions