Get each month balance with capping on redshift

user_15425078 0
Viewed 107

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 :

Result

Any thought ? Thanks !

balance rolling-sum amazon-redshift recursive-query sql
1 Answers

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.

user_11440350
Related