CODEWITHSUNDEEP
postgresqlrecursion
Nasia Ntalla
Nasia Ntalla

Reputation: 1789

Recursive Cumulative Sum up to a certain value Postgres

I have my data that looks like this:

user_id touchpoint_number   days_difference
1       1                   5
1       2                   20
1       3                   25
1       4                   10
2       1                   2
2       2                   30
2       3                   4

I would like to create one more column that would create a cumulative sum of the days_difference, partitioned by user_id, but would reset whenever the value reaches 30 and starts counting from 0. I have been trying to do it, but I couldn't figure it out how to do it in PostgreSQL, because it has to be recursive.

The outcome I would like to have would be something like:

user_id touchpoint_number   days_difference cum_sum_upto30
1       1                   5               5
1       2                   20              25
1       3                   25              0    --- new count all over again
1       4                   10              10
2       1                   2               2
2       2                   30              0    --- new count all over again
2       3                   4               4

Do you have any cool ideas how this could be done?

Upvotes: 0

Views: 593

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This should do what you want:

with cte as (
         select t.a, t.b, t.c, t.c as sumc
         from t
         where b = 1
         union all
         select t.a, t.b, t.c,
                (case when t.c + cte.sumc > 30 then 0 else t.c + cte.sumc end)
         from t join
              cte
              on t.b = cte.b + 1 and t.a = cte.a
        )
select *
from cte
order by a, b;

Here is a rextester.

Upvotes: 2

Related Questions