create table transactions
( o_id int,
cust_id int,order_date date,
amount int) ;
insert into transactions values
(1,1,'2020-01-15',150),
(2,1,'2020-02-10',150) ,
(3,2,'2020-01-16',150),
(4,2,'2020-02-25',150),
(5,3,'2020-01-10',150),
(6,3,'2020-02-20',150),
(7,4,'2020-01-20',150) ,
(8,5,'2020-02-20',150);
with cte as (
select *, datename(month, order_date) as month
from transactions
), cte2 as (
select cte.o_id as o_id1, cte.cust_id as c1, cte.order_date as od1, cte.month as mn1,
cte2.o_id as o2, cte2.cust_id as c2, cte2.order_date as od2, cte2.month as mn2
from cte
inner join cte as cte2
on cte.cust_id = cte2.cust_id
and datediff(month, cte.order_date, cte2.order_date) =1)
select *,
case when cust_id in(select c1 from cte2) then 'no' else 'yes' end churn
from cte;
/*
with cte as(select *,datename(month,order_date ) month from transactions),
cte2 as
(select cte.o_id as o_id1,cte.*cust_id as c1,cte.order_date od1, cte.month mn1, cte2.o_id as o2, cte2.cust_id as c2, cte2.order_date o2, cte2.month mn2 from cte
left join
cte te2
on cte.cust_id=cte2.cust_id
and datediff(month,cte.order_date, cte2.order_date)=1)
select * from cte2
Telegram: DB developers channel