I would like to get each month balance for those records, with a capping to 500 credits at the beginning of each month.
And I am a bit stuck because I think I can't simply do a rolling sum because the maximum balance for a customer is twice the amount of his new credits (I use 500 as a maximum in the example).
Here is my data :
CREATE TABLE table1 as (
SELECT 'A' as customer_id, 250 as new_credits, -62 as debit, 1 as month_nb
UNION ALL
SELECT 'A', 250, -84, 2
UNION ALL
SELECT 'A', 250, -8, 3
UNION ALL
SELECT 'A', 210, -400, 4
UNION ALL
SELECT 'A', 210, -162, 5
UNION ALL
SELECT 'A', 210, 0, 6
)
I would like to see these result :
Any thought ? Thanks !
I'm adding a new answer because the previous answer was out-of-date. I'm not sure what the exact syntax is for Redshift (the documentation does not seem totally updated), but here is the idea:
with recursive cte as (
select month_nb, customer_id, new_credits, debit, new_credits as starting_balance
from table1
where month_nb = 1
union all
select t1.month_nb, t1.customer_id, t1.new_credits, t1.debit,
least(500, cte.starting_balance + cte.debit + t1.new_credits)
from cte join
table1 t1
on t1.month_nb = cte.month_nb + 1 and t1.customer_id = cte.customer_id
)
select *
from cte;
For instance, I'm not sure if the recursive
keyword is needed.
And here is a db<>fiddle using Postgres.