MySQL 10강

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