计算用户登录留存情况

• 得到包含首次购买时间，最后一次购买时间的用户表
• 计算用户两次登录时间间隔，并使用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
)