Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE example (
  usr int
, month_series int
, eligibility int
, running_amt int
, new_amt int
);

INSERT INTO example (usr, month_series, eligibility, running_amt, new_amt)
VALUES
  (1,  1, 1,    0,   0)
, (1,  2, 0,  150, 150)
, (1,  3, 0,  150,   0)
, (1,  4, 1, 1000, 850)
, (1,  5, 0, 1200, 200)
, (1,  6, 0, 1200,   0)
, (1,  7, 1, 1200,   0)
, (1,  8, 0, 1500, 300)
, (1,  9, 0, 1550,  50)
, (1, 10, 0, 1600,  50)
, (1, 11, 1, 1600,   0)
, (1, 12, 1, 1600,   0)
;

CREATE TABLE



INSERT 0 12


SELECT usr, month_series, eligibility,running_amt, new_amt
     , CASE WHEN eligibility = 1 THEN sum(new_amt) OVER (PARTITION BY grp) ELSE 0 END AS desired_result 
FROM  (
   SELECT *
        , count(*) FILTER (WHERE eligibility = 1) OVER (ORDER  BY month_series DESC) AS grp
   FROM   example
   ) sub
ORDER  BY month_series;


usr
month_series
eligibility
running_amt
new_amt
desired_result


1
1
1
0
0
0

1
2
0
150
150
0

1
3
0
150
0
0

1
4
1
1000
850
1000

1
5
0
1200
200
0

1
6
0
1200
0
0

1
7
1
1200
0
200

1
8
0
1500
300
0

1
9
0
1550
50
0

1
10
0
1600
50
0

1
11
1
1600
0
400

1
12
1
1600
0
0



SELECT 12


-- we don't need "running_amt" for this

ALTER TABLE example DROP COLUMN running_amt;

SELECT usr, month_series, eligibility, new_amt
     , CASE WHEN eligibility = 1 THEN sum(new_amt) OVER (PARTITION BY grp) ELSE 0 END AS desired_result 
FROM  (
   SELECT *
        , count(*) FILTER (WHERE eligibility = 1) OVER (ORDER  BY month_series DESC) AS grp
   FROM   example
   ) sub
ORDER  BY month_series;

ALTER TABLE




usr
month_series
eligibility
new_amt
desired_result


1
1
1
0
0

1
2
0
150
0

1
3
0
0
0

1
4
1
850
1000

1
5
0
200
0

1
6
0
0
0

1
7
1
0
200

1
8
0
300
0

1
9
0
50
0

1
10
0
50
0

1
11
1
0
400

1
12
1
0
0



SELECT 12

usr	month_series	eligibility	running_amt	new_amt	desired_result
1	1	1	0	0	0
1	2	0	150	150	0
1	3	0	150	0	0
1	4	1	1000	850	1000
1	5	0	1200	200	0
1	6	0	1200	0	0
1	7	1	1200	0	200
1	8	0	1500	300	0
1	9	0	1550	50	0
1	10	0	1600	50	0
1	11	1	1600	0	400
1	12	1	1600	0	0