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

select c.tag
        ,round(count(c.uid)/(select count(distinct uid) from tb_user_log),2) as ratio
from
    (
      select a.uid
              ,case when timestampdiff(day,a.last_out,a.today_time) <=6 and b.last_out_times >1 then '忠实用户'
              when timestampdiff(day,a.last_out,a.today_time) <=6 and b.last_out_times =1 then '新晋用户'
              when timestampdiff(day,a.last_out,a.today_time) >6 and timestampdiff(day,a.last_out,a.today_time) <=29 then '沉睡用户'
              else '流失用户' end as tag
      from
          (
            select uid
                    ,(select max(out_time)  from tb_user_log) as today_time
                    ,max(out_time) last_out
                    ,max(date(out_time)) dt
            from tb_user_log
            group by uid) a,  #a表：用户最近登陆时间和今天的间隔
            (
              select uid
                      ,date(out_time) as dt
                      ,row_number() over(partition by uid order by date(out_time) asc) as last_out_times
              from tb_user_log) b  #b表：用户最近登陆时间是否为首次登陆
      where a.uid=b.uid and a.dt = b.dt) c #c表：用户最近登陆时间和首次登陆判定
group by c.tag
order by ratio desc;