I have table like this with hundreds of records : month_signup, nb_signups, month_purchase and nb_purchases

Let's say I want to calculate the signup to purchase ratio month after month.

Normaly I can juste divide nb_purchases/nb_signups*100 but here no.

I want to calculate a signup to purchase ratio with 1 month (or 30days) delay.

To let the signups the time to purchase, I want to do the nb_purchase from month 2 divided by nb_signups from month_1. So 20/100 for exemple in my table.

I tried this but really not sure.

SELECT
month_signup
,SAFE_DIVIDE(CASE WHEN purchase_month BETWEEN signups_month AND DATE_ADD(signups_month, INTERVAL 30 DAY) THEN nb_purchases ELSE NULL END, nb_signups)*100 AS sign_up_to_purchase_ratio

FROM table

ORDER BY 1

CodePudding user response：

You can use LEAD() function to get the next value of the current row, I'll provide a MySQL query syntax for this.

with cte as 
(select month_signup, nb_signups, lead(nb_purchases) over (order by month_signup) as 
 nextPr from MyData
 order by month_signup)
 select cte.month_signup, (nextPr/cte.nb_signups)*100 as per from cte
 where (nextPr/cte.nb_signups)*100 is not null;

You may replace (nextPr/cte.nb_signups) with the SAFE_DIVIDE function.

See the demo from db-fiddle.