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

select user_grade, 
       round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio
from ( SELECT uid,
              case 
              when (max(date(in_time)) BETWEEN '2021-10-29' and '2021-11-04') 
                   and (min(date(in_time)) <'2021-10-29' ) then '忠实用户'
              when min(date(in_time)) BETWEEN '2021-10-29' 
                   and '2021-11-04' then '新晋用户'
              when (max(date(in_time)) <= '2021-10-28') 
                    and (max(date(in_time)) >= '2021-10-06') then '沉睡用户'
              else '流失用户' end as user_grade
              FROM tb_user_log
              GROUP BY uid ) a
GROUP BY user_grade
ORDER BY ratio desc