题解 | #统计活跃间隔对用户分级结果#比较通俗的解法，就是判断条件比较繁琐

基本流程：

1. 先查询出所有日期中的最大值
2. 条件判断： 忠实用户--大于等于最大值减6并且不等于该用户的第一次登陆时间 新晋用户--大于等于最大值减6并且等于该用户的第一次登陆时间 沉睡用户--小于等于最大值减6且大于最大值减30 流失客户--小于等于最大值减30
3. 根据新生成字段分组求出各等级用户占总用户的比例

这里注意先要根据每个用户分组聚合得到每个用户最近登陆时间表

select t2.user_grade
,round(count(distinct t2.uid)/(select count(distinct uid) from tb_user_log),2) ratio
from (select uid
      ,case when 
      date_add(t1.dt,interval 6 day)>=(select max(date(in_time)) 
                                       from tb_user_log) 
      and (t1.uid,t1.dt) not in (select uid,min(date(in_time))
                                 from tb_user_log
                                 group by uid)
      then '忠实用户'
      when date_add(t1.dt,interval 6 day)>=(select max(date(in_time)) 
                                            from tb_user_log) 
      and (t1.uid,t1.dt) in (select uid,min(date(in_time))
                             from tb_user_log
                             group by uid)
      then '新晋用户'
      when date_add(t1.dt,interval 6 day)<=(select max(date(in_time)) 
                                            from tb_user_log)
      and  date_add(t1.dt,interval 30 day)>(select max(date(in_time)) 
                                            from tb_user_log)
      then '沉睡用户'
      else '流失用户'
      end user_grade
      from (select uid,max(date(in_time)) dt
            from tb_user_log
            group by uid) as t1
     ) as t2
group by t2.user_grade
order by ratio desc