Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


create table item (item_id int, cost_price int, date_purchase date);
insert into item values 
(1, 200, '2019-06-01'),
(2, 300, '2019-07-10'),
(3, 250, '2019-08-15'),
(4, 400, '2019-09-10');

create table payment (item_id int, payment int, payment_date date);
insert into payment values 
(1, 50, '2019-06-01'),
(1, 40, '2019-06-20'),
(2, 30, '2019-07-15'),
(1, 60, '2019-07-17'),
(2, 100, '2019-08-15'),
(3, 90, '2019-08-17'),
(4, 300, '2019-09-20'),
(1, 50, '2019-09-25');
4 rows affected

8 rows affected

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;


month
remaining_amount


2019-08
380

2019-06
110

2019-07
320

2019-09
430