逻辑清晰

with t as 
(
    select uid,max(date(in_time))over(partition by uid) as max_part_time,
               min(date(in_time))over(partition by uid) as min_part_time,
               date_add(max(date(out_time))over(),interval -6 day) as 7_time,
               date_add(max(date(out_time))over(),interval -29 day) as 29_time,
               (select count(distinct uid) from tb_user_log) as total_num
    from tb_user_log
)
select user_grade,round(count(1)/total_num,2) as ratio
from 
    (select distinct uid ,'忠实用户' as user_grade,total_num
    from t
    where min_part_time<7_time and max_part_time>=7_time
    union ALL
    select distinct uid,'新晋用户' as user_grade,total_num
    from t
    where min_part_time>=7_time
    union all
    select distinct uid,'沉睡用户' as user_grade,total_num
    from t 
    where max_part_time<7_time and max_part_time >=29_time
    union ALL
    select distinct uid,'流失用户' as user_grade,total_num
    from t
    where max_part_time<29_time
    ) t1
group by user_grade
order by ratio desc