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

select 
user_grade,
round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio
from(
     select uid,
      (CASE when DATEDIFF(td,first_reg)>6 and DATEDIFF(td,last_active)<=6 then "忠实用户"
            when DATEDIFF(td,first_reg)<=6 and DATEDIFF(td,last_active)<=6 then "新晋用户"
            when DATEDIFF(td,last_active)<=29 then "沉睡用户"
            when DATEDIFF(td,last_active)>29 then "流失用户"
            else "未分类" end ) user_grade
     from (
      select uid,
         min(in_time) first_reg,
         max(out_time) last_active,
         (select max(out_time) 
      from tb_user_log)td 
      from tb_user_log
      group by uid)a )b
GROUP BY user_grade
ORDER BY ratio desc

这道题目难在对原始数据时间字段 新建一个名字为用户类型的分类字段，结合题干给出的分类标准和数学的线段的思维我做了一张线段图如下图所示，这样更加直观明了

这幅图的水平轴代表的是时间，题目中的“今天”=11月4日(td)，“近7天”=10月29日， “近30天”=10月6日

原始表每一个用户有in_time和out_time两个时间 还有用户不止一次登入和退出

根据题目的意思我把四种用户表示为四个处于不同区间的线段，

①忠实用户：由图可知，td - min(in_time)>6 并且 td-last(out_time)<=6

②新晋用户：由图可知，td - min(in_time)<=6 并且 td-last(out_time)<=6

③沉睡用户：由图可知，td - last(out_time)>6 并且 td - ast(out_time)<= 29

④流失客户：有图可知，td - last(out_time)>29

有了这分类的基本逻辑接下来就是撸代码了，哈哈

第一步，查出每个用户的min(in_time)和last(out_time)以及时间标杆今天（td）

第二步，根据刚才分类的逻辑开始给uid标记用户类型

第三步，按照题目要求编辑每种用户类型的比率：