- 得到包含首次购买时间，最后一次购买时间的用户表
- 计算用户两次登录时间间隔，并使用case when 对购买日期进行筛选
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) "用户个数"
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
)
