Thank you. Do you think this will work for not consecutive month order date and different year order date. Suppose cust_id 1 has ordered in Jan, Feb and May. So ideally this id should come for Feb churn analysis but this will not come with this solution approach. Please correct me if I am wrong.
select distinct cust_id from (select * ,min(order_date) over(partition by cust_id order by order_date) as min_date ,max(order_date) over(partition by cust_id order by order_date desc) as max_date from transactions)a where (month(max_date)-month(min_date))=1
Thank you. Do you think this will work for not consecutive month order date and different year order date. Suppose cust_id 1 has ordered in Jan, Feb and May. So ideally this id should come for Feb churn analysis but this will not come with this solution approach.
Please correct me if I am wrong.
select distinct cust_id
from
(select *
,min(order_date) over(partition by cust_id order by order_date) as min_date
,max(order_date) over(partition by cust_id order by order_date desc) as max_date
from transactions)a
where (month(max_date)-month(min_date))=1