select case when diff >= 90 then "Churn" else "N-Churn" end churn_type, count(distinct customerNumber) as N_CUS
from (select customerNumber, "2005-06-01" as Now_d, max_order, datediff("2005-06-01", max_order) as Diff
from (select customerNumber, max(orderDate) as max_order from classicmodels.orders group by customerNumber) as A) as B
group by 1;
Churn 69
N-Churn 29
select C.productLine, count(distinct B.customerNumber)
from classicmodels.orderdetails as A left join classicmodels.orders as B on A.orderNumber = B.orderNumber
left join classicmodels.products as C on A.productCode = C.productCode group by 1;
Classic Cars 94
Motorcycles 55
Planes 52
Ships 51
Trains 36
Trucks and Buses 50
Vintage Cars 90
select D.churn_type, C.productLine, count(distinct B.customerNumber)
from classicmodels.orderdetails as A left join classicmodels.orders as B on A.orderNumber = B.orderNumber
left join classicmodels.products as C on A.productCode = C.productCode
left join classicmodels.churn_list as D on B.customerNumber = D.customerNumber group by 1, 2;
Churn Classic Cars 66
Churn Motorcycles 34
Churn Planes 35
Churn Ships 33
Churn Trains 23
