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

1.第一张表记录每个uid和对应的最近日期，最早日期

2.第二张表记录表中所有的不同uid总和，和当前日期

3.第三张表记录uid，最近日期距离当前日期天数，最早日期距离当前日期天数，所有uid总和

4.第四张表根据题目要求进行分类

最后进行汇总

select user_grade,round(count(uid)/max(total_num),2) as ratio
from 
(select uid, total_num,
        CASE
            WHEN last_time >= 30 THEN "流失用户"
            WHEN last_time >= 7 THEN "沉睡用户"
            WHEN first_time < 7 THEN "新晋用户"
            ELSE "忠实用户"
        END as user_grade
from
(select uid,timestampdiff(day,last_dt,now) as last_time,
timestampdiff(day,first_dt,now) as first_time,
total_num
from 
(select uid,max(date(out_time)) as last_dt,min(date(in_time)) as first_dt
from tb_user_log group by  uid)t1
left join
(select count(distinct uid) as total_num,max(date(out_time)) as now from tb_user_log)t2 on 1)t3)t4
group by user_grade
order by ratio desc