Monthly cumulative differences calculation in postgres-CodePudding

I am trying to write an sql script in postgres that find cumulative difference in total price and repayment amount. I have two tables as shown below. I have gone through solution provided here but it doesn't address my question.

item table

item_id  cost_price    date_purchase
1        200           01-06-2019
2        300           10-07-2019
3        250           15-08-2019
4        400           10-09-2019

payment table
item id  payment       payment date
1          50           01-06-2019
1          40           20-06-2019
2          30           15-07-2019
1          60           17-07-2019
2          100          15-08-2019
3          90           17-08-2019
4          300          20-09-2019
1          50           25-09-2019

Expected result

Month                        Remaining amount
06_2019       (200 - 90) = 110
07_2019       (200  300) - (90   30   60) = 320
08_2019       (200  300   250) - (90   90   100   90) = 380
09_2019       (200   300   250   400) - (90   90   190   300   50) = 430

CodePudding user response：

You can do that by SUMs with WINDOWING function that's uses ORDER BY month. But give us the DDL of your table to be helped more...

CodePudding user response：

Since your example ignores the item_id in the results, you can combine purchases and payments into a simple ledger and then use a window function to get a running sum:

with ledger as (
  select to_char(date_purchase, 'YYYY-MM') as xact_month, cost_price as amount from item
  union all
  select to_char(payment_date, 'YYYY-MM'), payment * -1 from payment
)
select distinct xact_month as month, 
       sum(amount) over (order by xact_month) as remaining_amount
  from ledger;

Working fiddle.