将问题做以下分解

  1. 分别求用户在7天内，7-30天，和30天以上的登录次数
  2. 根据登录次数来判断用户属性
  3. left join b on 1的语法，将uid的总数关联到原始的查询上
select user_grade,
round(count(1) / max(cnt), 2)
from (
select 
       case when d7 > 0 and d30 = 0 and dm =0 then '新晋用户'
            when d7 > 0 and (d30 > 0 or dm > 0) then '忠实用户'
            when d7 = 0 and d30 > 0 and dm = 0 then '沉睡用户'
            else '流失用户' end user_grade
from 
(
select uid,
max(in_time) as last_active,
count(case when datediff('2021-11-04', in_time) < 7 then 1 else null end) d7,
count(case when datediff('2021-11-04', in_time) >= 7 and datediff(in_time, now()) < 30 then 1 else null end) d30,
count(case when datediff('2021-11-04', in_time) >= 30 then 1 else null end) dm
from tb_user_log
group by 1) t1
) t2
left join (select count(distinct uid) cnt from tb_user_log) t3 
on 1
group by 1 
order by 2 desc