select level,round(count(*)/sum(count(*)) over(),2) ratio
from (
select uid,case when datediff(today,first)<7 then '新晋用户'
when datediff(today,last) <= 7 and datediff(today,first)>7 then '忠实用户'
when datediff(today,last) >=7  and  datediff(today,last) < 30 then '沉睡用户'
when datediff(today,last) >= 30 then '流失用户'
end level
from (
select uid,min(date(in_time)) first,max(date(out_time)) last,max(max(date(in_time))) over() today
from tb_user_log
group by uid) T1
) t2
group by level
order by ratio desc

思路

1.计算每个用户的注册时间和最后活跃时间以及今天日期

2.注册时间与今天日期小于7 新用户；最后活跃日期与今天日期小于等于7 and 注册时间与今天日期大于7 忠实用户；最后活跃日期与今天大于等于7小于30 沉睡用户，大于30流失用户

3.计算每个等级用户的个数和总用户数