from tb_user_log
where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day)
from tb_user_log))
select case when value=1 then '忠实用户'
when value=2 then '新晋用户'
when value=3 then '沉睡用户'
else '流失用户' end as user_grade,
round(count(value)/(select COUNT(distinct uid)
from tb_user_log),2)
from (select uid, 1 as value
from a1
right join tb_user_log tl using (uid)
group by uid
having count(tl.in_time) > 1 #忠实用户
union all
select a1.uid, 2 as value
from a1
right join tb_user_log tl using (uid)
group by uid
having count(tl.in_time) = 1 #新晋用户
union all
select uid, 3 as value
from tb_user_log
where uid not in (select uid
from tb_user_log
where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day)
from tb_user_log))
and date(in_time) >= (select date_sub(max(date(in_time)), interval 30 day)
from tb_user_log) #沉睡用户
union all
select uid, 4 as value
from tb_user_log
where uid not in (select uid
from tb_user_log
where date(in_time) >= (select date_sub(max(date(in_time)), interval 6 day)
from tb_user_log))
and date(in_time) < (select date_sub(max(date(in_time)), interval 30 day)
from tb_user_log) #流失用户
)b
group by user_grade