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

WITH uid_dt as (
SELECT distinct uid, DATE(in_time) as dt FROM tb_user_log
UNION 
SELECT distinct uid, DATE(out_time) as dt FROM tb_user_log
)



select user_grade,round(cnt/s,2) as ratio
from
(
select user_grade, count(user_grade) as cnt,(select count(distinct(uid)) from uid_dt) as s
from(

select uid,dt_first,
        (case 
         when dt_first>=
        (select date_sub(dt_now,INTERVAL 6 day) as dt_7_day
        from(select max(dt) as dt_now from uid_dt)l)
        then '新晋用户' 
         when dt_last >=
         (select date_sub(dt_now,INTERVAL 6 day) as dt_7_day
        from(select max(dt) as dt_now from uid_dt)l)
         and 
         dt_first < 
         (select date_sub(dt_now,INTERVAL 6 day) as dt_7_day
        from(select max(dt) as dt_now from uid_dt)l)
         then '忠实用户'
         when dt_last < 
         (select date_sub(dt_now,INTERVAL 6 day) as dt_7_day
        from(select max(dt) as dt_now from uid_dt)l)
         and dt_last >= 
         (select date_sub(dt_now,INTERVAL 29 day) as dt_30_day
        from(select max(dt) as dt_now from uid_dt)l)
         
        then '沉睡用户'
         when dt_last < 
         (select date_sub(dt_now,INTERVAL 29 day) as dt_30_day
        from(select max(dt) as dt_now from uid_dt)l)
        then '流失用户'
        else 0 end) as user_grade 
        
from(select uid,min(dt) as dt_first ,max(dt) as dt_last from uid_dt group by uid)ll
)lll
group by user_grade

)llll
order by ratio desc