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

1.    从tb_user_log表中选出各个用户的最近一次登录的日期及uid，在where中添加子查询即可：where (uid,in_time) in (select uid,max(in_time) from tb_user_log group by uid)

2.    添加辅助列用以判断是否为近七日新用户，通过 datediff( ) 函数比较用户最早登录日期与今日日期差值小于7的用户，如果是则为1反之为0，并命名为if_new ：case when uid in (select uid from tb_user_log group by uid having datediff((select date(max(in_time)) from tb_user_log),date(min(in_time)))<7) then 1 else 0 end   as   if_new

3.    基于前两步查询出的子表，使用case when函数划分用户等级，命名为user_grade：

when datediff((select date(max(in_time)) from tb_user_log),date(in_time))<7 and if_new=0 then '忠实用户'     --近七日登录且不为七日新用户

when datediff((select date(max(in_time)) from tb_user_log),date(in_time))<7 and if_new=1 then '新晋用户'    --近七日登录且为七日新用户

when datediff((select date(max(in_time)) from tb_user_log),date(in_time)) between 7 and 29 then '沉睡用户'   
when datediff((select date(max(in_time)) from tb_user_log),date(in_time))>29 then '流失用户'     end     user_grade    

最后，再套一层查询，并按user_grade分类，ratio排序即可：

select user_grade,round(count(*)/(select count(distinct uid) from tb_user_log),2) ratio
from(
select case when datediff((select date(max(in_time)) from tb_user_log),dt)<7 and if_new=0 then '忠实用户'
            when datediff((select date(max(in_time)) from tb_user_log),dt)<7 and if_new=1 then '新晋用户'
            when datediff((select date(max(in_time)) from tb_user_log),dt) between 7 and 29 then '沉睡用户'
            when datediff((select date(max(in_time)) from tb_user_log),dt)>29 then '流失用户' end user_grade
from(
select uid,date(in_time) dt,
case when uid in (select uid from tb_user_log group by uid having datediff((select date(max(in_time)) from tb_user_log),date(min(in_time)))<7) 
then 1 else 0 end if_new
from tb_user_log
where (uid,in_time) in (select uid,max(in_time) from tb_user_log group by uid)
) a
) b
group by user_grade
order by ratio desc