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

with a as(
select uid,artical_id,date(in_time) as dt
from tb_user_log
union 
select uid,artical_id,date(out_time) as dt
from tb_user_log),
b as (
    select uid,min(dt) as first_date,
    max(dt) as last_date
    from a
    group by uid
),
cur as(
     SELECT MAX(dt) AS cur_date 
    FROM a
),
user_num as(
    select count(distinct uid) as cnt from a
)

select user_grade,
round(count(uid)/cnt,2) as ratio
from(
select 
distinct a.uid,
case when last_date between date_sub(cur_date,interval 29 day) and date_sub(cur_date,interval 7 day)
then '沉睡用户'

when  last_date  <= date_sub(cur_date,interval 29 day)
then '流失用户'

when (first_date between date_sub(cur_date,interval 6 day) and cur_date)
then '新晋用户' 

when (first_date < date_sub(cur_date,interval 6 day)) and (last_date between date_sub(cur_date,interval 6 day) and cur_date)
then '忠实用户'
end as user_grade
from a
join b on a.uid = b.uid
cross join cur
)d
cross join user_num
group by user_grade,cnt
order by ratio desc

二刷