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

绕来绕去终于想到比较清晰的方法了

核心思路：用每个用户的最小日期、最大日期来形成区间，即表b，来框定用户等级。

难点：

1、对于题中用户等级的理解：用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。

其实就是把时间分区间，共分成3个区间：今日——第7天——第30天——大于30天

                                                max_dt<——>min_dt 

2、uid、min_dt、max_dt、今日：select max(dt) from a

新晋用户(近7天新增)：即 用户的最小日期 min_dt 在7天内，即 datediff((select max(dt) from a),min_dt) between 0 and 6

忠实用户(近7天活跃过且非新晋用户)：1、非新晋用户：用户最小日期不在7天内，即大于7天即 datediff((select max(dt) from a),min_dt)>6；2、近7天活跃过：只要用户的最大日期在7天内，那他一定在7内活跃过，即 datediff((select max(dt) from a),max_dt) between 0 and 6

沉睡用户(近7天未活跃但更早前活跃过)：1、7天未活跃：最大日期max_dt 不在7天内即datediff((select max(dt) from a),max_dt)>6   2、更早前活跃：最大日期max_dt 要在30天以内 即datediff((select max(dt) from a),max_dt)<=29流失用户(近30天未活跃但更早前活跃过):最大日期max_dt 大于30天，即datediff((select max(dt) from a),max_dt)>29

with a as (select uid,date(in_time) as dt from tb_user_log
union all   #使用union all保证了即使出现跨天的情况也可行
select uid,date(out_time) as dt from tb_user_log),
b as (
select
uid,min(dt) as min_dt,max(dt) as max_dt
from a 
group by uid
),
c as (
select 
uid,
case when datediff((select max(dt) from a),min_dt) between 0 and 6 then '新晋用户'
    when datediff((select max(dt) from a),max_dt) between 0 and 6 and datediff((select max(dt) from a),min_dt)>6 then '忠实用户'
    when datediff((select max(dt) from a),max_dt)>6 and datediff((select max(dt) from a),max_dt)<=29 then '沉睡用户'
    when datediff((select max(dt) from a),max_dt)>29 then '流失用户' end as flag
from b)

select 
flag,
round(count(distinct uid)/(select count(*) from b),2) as ratio
from c
group by flag
order by ratio desc