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

select user_grade,round(count(*)/user_cnt,2) as ratio
from
(select distinct uid,(select count(distinct uid) from tb_user_log ) as user_cnt,
    case 
    when last_dt_diff >=30 then '流失用户'
    when last_dt_diff >=7 then '沉睡用户'
    when new_dt_diff <7 then '新晋用户'
    else '忠实用户'
    end as user_grade
from(
    select uid,
    timestampdiff(day,date(max(out_time)),(select date(max(out_time)) from tb_user_log)) as last_dt_diff,
    timestampdiff(day,date(min(in_time)),(select date(max(out_time)) from tb_user_log)) as new_dt_diff
from tb_user_log
group by uid
)a)b
group by user_grade
order by ratio desc

闭包是等号