笨办法，一步一步推
-----------------
with
t1 as(
    select dates
    from
    ((select date(in_time) as dates from tb_user_log group by date(in_time))
     union
     (select date(out_time) as dates from tb_user_log group by date(out_time))
    ) a),
t2 as(
    select
        t1.dates,
        t0.uid
    from t1 left join tb_user_log t0 on (t1.dates=date(t0.in_time) or t1.dates=date(t0.out_time))
    where t0.id is not null
    group by t1.dates,t0.uid),
t3 as(select * from t2 where dates>=date_sub('2021-11-04',interval 6 day)),
t4 as(select * from t2 where dates<date_sub('2021-11-04',interval 6 day)),
t5 as(select * from t3 where uid not in(select uid from t4)),
t6 as(select * from t3 where uid not in(select uid from t5)),
t7 as(select * from t4 where uid not in(select uid from t3) and dates>=date_sub('2021-11-04',interval 29 day)),
t8 as(
    select * from t2 
    where dates<date_sub('2021-11-04',interval 29 day) 
    and uid not in(select uid from t2 where dates>=date_sub('2021-11-04',interval 29 day))),
t9 as(select count(distinct uid) as total_users from t2),
t10 as(select count(distinct uid) as loyal_users from t6),
t11 as(select count(distinct uid) as new_users from t5),
t12 as(select count(distinct uid) as sleeping_users from t7),
t13 as(select count(distinct uid) as left_users from t8)

select '忠实用户' as user_grade,round(loyal_users/total_users,2) as ratio from t9 join t10 on 1=1
union
select '新晋用户' as user_grade,round(new_users/total_users,2) as ratio from t9 join t11 on 1=1
union
select '沉睡用户' as user_grade,round(sleeping_users/total_users,2) as ratio from t9 join t12 on 1=1
union
select '流失用户' as user_grade,round(left_users/total_users,2) as ratio from t9 join t13 on 1=1