题解 | #统计活跃间隔对用户分级结果#

#先筛选每个用户初始的登陆日期
with init(uid,Time) as (
select uid,date(min(in_time) )
from tb_user_log
group by uid
order by uid)
#先求个数再求比例
select user_grade,round(count(distinct uid)/min(min_hr),2) ratio
from
(select uid,
case when max(date(in_time))>=date_sub(min(today),interval 6 day) and Time <date_sub(min(today),interval 6 day)
            then "忠实用户"
     when Time>=date_sub(min(today),interval 6 day)
            then "新晋用户"
     when Time<date_sub(min(today),interval 6 day) and max(date(in_time))<date_sub(min(today),interval 29 day)
            then "流失用户"
     else "沉睡用户" end  as user_grade,min(hr) as min_hr
from
(select * 
from
(select *
from
(select date(max(in_time)) today,count(distinct uid) hr
from tb_user_log )a,tb_user_log)b inner join init using(uid)
) c
group by c.uid
order by uid)d
group by user_grade
order by ratio desc