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

1.首先要贴标签

“今天”其实是整个表中最大的一天，其余要寻找用户就得按照用户分组找每个用户的最大最小的天来判断用户的等级。

2.最后算比例的时候在分母处应该一个用户不能算多次，所以要用distinct uid。

SELECT user_grade, ROUND(COUNT(uid)/(SELECT COUNT(distinct uid) FROM tb_user_log),2) ratio
FROM
(
SELECT uid,case 
    when datediff((select max(in_time) from tb_user_log),max(in_time))<7 and datediff((select max(in_time) from tb_user_log),min(in_time))>=7 then '忠实用户'
    when datediff((select max(in_time) from tb_user_log),min(in_time))<7 then '新晋用户'
    when datediff((select max(in_time) from tb_user_log),max(in_time))>=30 then '流失用户'
    else '沉睡用户' end user_grade
from tb_user_log
group by uid
)t1
GROUP BY user_grade
order by ratio desc;