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

select user_grade, round(count(uid) / max(uc), 2) as ratio
from (select uid,
             uc,
             case
                 when t2 >= 30 then '流失用户'
                 when t2 >= 7 then '沉睡用户'
                 when t1 >= 7 then '忠实用户'
                 else '新晋用户' end as user_grade
      from (select uid, timestampdiff(day, ft, mt) as t1, timestampdiff(day, lt, mt) as t2, uc
            from (select max(date(in_time)) as mt, count(distinct uid) as uc
                  from tb_user_log) a,
                 (select uid, max(date(in_time)) as lt, min(date(in_time)) as ft
                  from tb_user_log
                  group by uid) b) c) d
group by user_grade
order by ratio desc;