一、思路：其实比较清晰，就是每个用户最早min、最晚max活跃时间与T、T-6、T-29之间的关系来划分。用case when分组即可。
二、步骤：
1、先把intime\outtime通过union联立，
2、再用窗口函数min和max提出每个用户最早、最晚活跃时间，用窗口函数count计算总用户数，
3、用case when划分组别，
4、根据用户组别分组、计算。

with tb1 as(
select uid, in_time as active_day from tb_user_log
union all 
select uid, out_time as active_day from tb_user_log)

select user_grade, round(count(user_grade)/max(total_num),2) as ratio
from(
select
case when min_d<date_sub(T,interval 6 day) and max_d>=date_sub(T,interval 6 day) then '忠实用户'
when min_d between date_sub(T,interval 6 day) and T then '新晋用户'
when max_d>=date_sub(T,interval 29 day) and max_d<date_sub(T,interval 6 day) then '沉睡用户'
when max_d<date_sub(T,interval 29 day) then '流失用户' end as user_grade,
count(uid)over() as total_num
from(
select distinct uid, '20211104' as T, 
date(min(active_day)over(partition by uid)) as min_d,
date(max(active_day)over(partition by uid)) as max_d
from tb1
) as tb2
) as tb3
group by user_grade
order by user_grade, ratio desc