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

1. 活跃日期（包括in和out）——用union连接in和out，得到q1

select uid,date(in_time) as act_date
       from tb_user_log
        union 
        select uid,date(out_time) as act_date
        from tb_user_log
        order by uid,act_date

2. 得到每个用户的首次活跃时间与最近活跃时间q2

with q1 as 
(select uid,date(in_time) as act_date
        from tb_user_log
        union 
        select uid,date(out_time) as act_date
        from tb_user_log
        order by uid,act_date)

select uid, max(act_date) as latest_date,min(act_date) as first_date
    from q1
    group by uid

3. 根据首次活跃时间、最近活跃时间与当天的时间差定义用户分类标签，得到q3

select uid,
case when datediff((select max(act_date) from q1),latest_date)>29
then '流失用户'
when datediff((select max(act_date) from q1),latest_date)>6
then '沉睡用户'
when datediff((select max(act_date) from q1),first_date)<=6 
then '新晋用户'
else '忠实用户' end as user_grade
from
   (select uid, max(act_date) as latest_date,min(act_date) as first_date
    from q1
    group by uid
   ) as q2

4. 计算各类用户占比q4

with q1 as 
(select uid,date(in_time) as act_date
        from tb_user_log
        union 
        select uid,date(out_time) as act_date
        from tb_user_log
        order by uid,act_date)

select user_grade,
round(count(user_grade)/(select count(distinct uid) 
                         from tb_user_log),2) as ratio
from 
(select uid,
case when datediff((select max(act_date) from q1),latest_date)>29
then '流失用户'
when datediff((select max(act_date) from q1),latest_date)>6
then '沉睡用户'
when datediff((select max(act_date) from q1),first_date)<=6 
then '新晋用户'
else '忠实用户' end as user_grade
from
   (select uid, max(act_date) as latest_date,min(act_date) as first_date
    from q1
    group by uid
   ) as q2
 ) as q3
 group by user_grade
 order by ratio desc,user_grade