select
(case when t3.day_value=1 then "次日留存"
when t3.day_value = 3 then "三日留存"
when t3.day_value = 7 then "七日留存"
else "其他"
end
) as type
,count(t3.user_id) "用戶個數(shù)"
from
(select
t1.user_id
,t1.first_time
,t2.last_time
,datediff(t2.last_time,t1.first_time) day_value
from
(select
user_id
,date(min(pur_dt)) first_time
from cd_now
group by
user_id) t1
left join
(select
user_id
,date(max(pur_dt)) last_time
from cd_now
group by
user_id
) t2
on t1.user_id=t2.user_id
) t3
group by
(case when t3.day_value=1 then "次日留存"
when t3.day_value = 3 then "三日留存"
when t3.day_value = 7 then "七日留存"
else "其他"
end
)
