with tmp1 as (
    select uid, min(date(in_time)) as first_login_date
    from tb_user_log -- 新晋用户 
    group by uid
    having first_login_date between '2021-11-04' + INTERVAL - 6 day and '2021-11-04'
),

     tmp2 as (
         select uid
         from tb_user_log -- 近七天活跃过的用户
         where date(out_time) <= '2021-11-04'
           and (date(in_time) + INTERVAL 6 day >= '2021-11-04')
         group by uid
     ),
     tmp3 as (
         select uid
         from tb_user_log -- 近三十天活跃过的用户
         where date(out_time) <= '2021-11-04'
           and (date(in_time) + INTERVAL 29 day >= '2021-11-04')
         group by uid
     ),

     tmp4 as (
         select t.uid
              , sum(count(distinct t.uid)) over () total_user
              , max(case
                        when t2.uid is not null and t1.uid is null then "忠实用户"
                        when t1.uid is not null then "新晋用户"
                        when t2.uid is null and t3.uid is not null then "沉睡用户"
                        when t.uid is not null and t3.uid is null then "流失用户"
             end)                                  user_grade-- 忠实用户
         from tb_user_log t
                  left join tmp1 t1 on t.uid = t1.uid
                  left join tmp2 t2 on t.uid = t2.uid
                  left join tmp3 t3 on t.uid = t3.uid
         group by uid)
select user_grade, round(count(user_grade) / max(total_user), 2) ratio
from tmp4
group by user_grade
order by ratio desc, user_grade

感觉好蠢都没想到最大活跃时间