[SQL] SQL - Recursive and iterative moving average

I have all the necesary fields to elaborate a retention matrix like this one:



That is to say, I have "Cohort_Month" (which is the month of the first transaction of each customer), "Period_Number" (which is the "Month XX" value for each row, or put another way, the number of months between each cohort and each transaction done in that cohort), and "Retention" (which is just a value for each Cohort_Month/Period_Number duplex).

What I need to do is extrapolate the retention values so that the matrix has no null values, taking the moving average of the 2 previous periods (and being the same value when there is only 1 previous period).

Thus, for the duplex March 2017/Month 04, the retention value should be 99.7775%, that is, the mean of the 2 values of the previous cohorts for the same period number (99.945% and 99.610%)

This is easily achieved by a windows function and the classic avg + rows between, preceding and following function.

However my problem is that, when that retention value is calculed by a moving average for that tuple, I need that new value to stay, and be used in the next cohort/month tuple. In this case, I need the 99.7775% value and the previous one (99.945) to be considered in the calculation of the value of the April 2017/Month 04 duplex, and follow that pattern until all the null values are filled with new values.

In python, I easily achieve that using a loop, but I need that to be done in SQL and I'm struggling a lot. I've seen related questions like this or this with no luck.

I'm using DBT. Thanks a lot!

Continue reading...