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