By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table purchases(id int, date date, purchase int);
insert into purchases values(1, '2020-01', 200);
insert into purchases values(2, '2020-12', 100);
insert into purchases values(2, '2020-03', 150);
insert into purchases values(3, '2020-03', 200);
insert into purchases values(1, '2020-07', 120);
insert into purchases values(1, '2020-08', 300);
insert into purchases values(3, '2020-05', 250);
with cte as
(
select id,date,purchase,
min(date)over(partition by id) FirstPurchaseMonth from purchases
)
,cte2 as
(
select substr(date,6,2)-substr(firstpurchasemonth,6,2) Purchasemonth, max(FirstPurchaseMonth)firstpurchasemonth,
purchase,sum(purchase)total from cte
group by firstpurchasemonth,substr(date,6,2)-substr(firstpurchasemonth,6,2)
)
select purchasemonth,firstpurchasemonth,sum(total)over(partition by firstpurchasemonth order by purchasemonth)total
from cte2
| Purchasemonth | firstpurchasemonth | total |
|---|---|---|
| 0 | 2020-01 | 200 |
| 6 | 2020-01 | 320 |
| 7 | 2020-01 | 620 |
| 0 | 2020-03 | 350 |
| 2 | 2020-03 | 600 |
| 9 | 2020-03 | 700 |