Running Sum of Amount Since Last eligible

Question

I have a table with users, a month series, a flag for eligibility, a running sum of an amount, and an incremental amount column (example data below). I need to find the amount that has accumulated for each user since the last eligibility flag has appeared.

User	Month	Eligibility	RunningAmt	new Amt
1	Jan 20	1	0	0
1	Feb 20	0	150	150
1	Mar 20	0	150	0
1	Apr 20	1	1000	850
1	May 20	0	1200	200
1	Jun 20	0	1200	0
1	Jul 20	1	1200	0
1	Aug 20	0	1500	300
1	Sep 20	0	1550	50
1	Oct 20	0	1600	50
1	Nov 20	1	1600	0
1	Dec 20	1	1600	0

create table example (
    user int,
    month_series int,
    eligibility int,
    running_amt int,
    new_amt int
);
insert into example
    (user, 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);

I have tried many kinds of lag() functions but can't seem to get anything to work. What I want is a column that resets to zero when the eligibility flag is 1 and adds the new amounts in between, essentially:

User	Month	Eligibility	RunningAmt	new Amt	desired result
1	Jan 20	1	0	0	0
1	Feb 20	0	150	150	0
1	Mar 20	0	150	0	0
1	Apr 20	1	1000	850	1000
1	May 20	0	1200	200	0
1	Jun 20	0	1200	0	0
1	Jul 20	1	1200	0	200
1	Aug 20	0	1500	300	0
1	Sep 20	0	1550	50	0
1	Oct 20	0	1600	50	0
1	Nov 20	1	1600	0	400
1	Dec 20	1	1600	0	0

Erwin Brandstetter · Answer 1 · 2023-01-13T01:07:36.233

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;

fiddle

Note that we don't need the redundant column running_amt at all for this. (Typically, redundant data should not be stored in the table.)

Also note how groups (grp) are formed with descending sort order (ORDER BY month_series DESC). This avoids an off-by-1 error that would arise from starting each group with the first 0 after a 1. Counting top-down results in the group numbers we need.

Basics for this query technique:

Select longest continuous sequence

About the aggregate FILTER clause (which can be combined with the OVER clause of window functions):

Aggregate columns with additional (distinct) filters

Aside: "user" is a reserved word. You would have to double-quote. But rather don't use reserved words as identifiers at all. Working with usr instead.

Running Sum of Amount Since Last eligible

1 Answers1