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

with hebing as
(select uid,artical_id,date(in_time) as date
from tb_user_log
union
select uid,artical_id,date(out_time) as date from tb_user_log),

cur_date as
(select max(date) as cur
from hebing),

min_max as 
(select uid,min(date) as first_date,max(date) as max_date
from hebing
group by uid),

user_cnt as(
    select count(distinct uid) as num from hebing
)

select user_grade,round(count(user_grade)/(select num from user_cnt),2) as ratio
from
(select distinct hb.uid,
(case when first_date between date_sub(cur,interval 6 day) and cur then '新晋用户'
when max_date between date_sub(cur,interval 6 day) and cur then '忠实用户'
when max_date between  date_sub(cur,interval 29 day) and date_sub(cur,interval 6 day) then '沉睡用户'
when max_date < date_sub(cur,interval 29 day) then '流失用户'
else null end) user_grade
from hebing hb
cross join cur_date
join min_max mm on mm.uid = hb.uid)a
cross join user_cnt
group by user_grade
order by ratio desc