比较通俗的一种“笨”方法#case when

本题最大的难点在于如何限定用户等级的范围：（以我的理解）

忠实用户(近7天活跃过且非新晋用户)：最近活跃在7天内，最早活跃不在7天内

新晋用户(近7天新增)：最早活跃在7天内

沉睡用户(近7天未活跃但更早前活跃过)：最近活跃不在近7天但在30天内活跃过

流失用户(近30天未活跃但更早前活跃过)：最近活跃不在近30天

这其中，最近活跃是max(in_time) 最早活跃是min(in_time)

因此，需要建一张由uid,max(in_time),min(in_time)构成的表备用

select uid,
         date(max(in_time)) max,
         date(min(in_time)) min
        from tb_user_log
        group by uid

接下来就好办事了，请注意完整代码中的每一个‘xx用户’和‘case when’，也注意between and 和 AND。 以下是完整代码：

select judge user_grade,round(count(judge)/(select count(distinct uid) from tb_user_log),2) ratio
from
    (select uid,max,min,
    case when max between (date_add((select date(max(in_time)) from tb_user_log),interval -6 day))
                     and (select date(max(in_time)) from tb_user_log)   
                  AND min<(date_add((select date(max(in_time)) from tb_user_log),interval -6 day))     
                  then '忠实用户' 
         when min between (date_add((select date(max(in_time)) from tb_user_log),interval -6 day))
                     and (select date(max(in_time)) from tb_user_log)
                  then '新晋用户'
         when max < (date_add((select date(max(in_time)) from tb_user_log),interval -6 day))         
                  AND max between (date_add((select date(max(in_time)) from tb_user_log),interval -29 day))
                              and (date_add((select date(max(in_time)) from tb_user_log),interval -6 day))
                  then '沉睡用户'
         when max <(date_add((select date(max(in_time)) from tb_user_log),interval -29 day))
                  then '流失用户' 
    else 0 end judge
    from 
        (select uid,
         date(max(in_time)) max,
         date(min(in_time)) min
        from tb_user_log
        group by uid
        ) AS n 
    ) AS nn
group by judge
order by ratio desc

有错误请告知我，会及时尽快更正！