1

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

3
  • +1 pretty much the same as my solution - thanks
    – Guy Bowden
    Mar 4 at 12:01
  • @GuyBowden I'd prefer to use minimal amount of window definitions...
    – Akina
    Mar 4 at 12:02
  • thanks for the additional unique tip
    – Guy Bowden
    Mar 4 at 12:02
0

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

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.