题解 | #统计活跃间隔对用户分级结果#

-- 我的小坑点是在分级时，新晋和流失比较好确认，沉默放在else后面
select user_grade,round(count(1)/(select count(distinct uid) from tb_user_log),2) as ratio
from
-- 第二步：给用户分等级
(select uid,
case when new_date_diff<7 then '新晋用户'
     when last_date_diff<7 and new_date_diff>=7 then '忠实用户'
     when last_date_diff>=30 then '流失用户'
     else '沉睡用户'
     end as user_grade
from 
-- 第一步：先找出每个用户首日活跃距今天数以及最后一次活跃距今天数的表
    (select uid,
    datediff((select max(date(out_time)) from tb_user_log),date(min(in_time))) as new_date_diff,
    datediff((select max(date(out_time)) from tb_user_log),date(max(out_time))) as last_date_diff
    from tb_user_log
    group by uid) t1) t2
group by user_grade
order by ratio desc