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

select user_grade,round(count(1)/(select count(distinct uid) from tb_user_log),2) as ratio
from(
select uid,(case when timestampdiff(day,in_time,cur_dt) < 7 then '新晋用户'
    when timestampdiff(day,in_time,cur_dt) >= 7 and timestampdiff(day,out_time,cur_dt) < 7 then '忠实用户'
    when timestampdiff(day,out_time,cur_dt) >= 7 and timestampdiff(day,out_time,cur_dt) < 30 then '沉睡用户'
    when timestampdiff(day,out_time,cur_dt) >= 30 then '流失用户' end) as user_grade
from(
select uid,min(in_time) as in_time,max(out_time) as out_time from tb_user_log
group by uid) as t1
left join (select max(out_time) as cur_dt from tb_user_log) as t2
on 1) as tt
group by user_grade
order by ratio desc

case whend的用法，提取当前日期cur_dt，最早登陆时间用来判断新晋用户，最晚登陆时间用来判断活跃天数，最后分组聚合求和即可