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

1.标记每条记录出现的时间是7天内还是30天内或是30天外，0,1 7d,30d,da30

2.按照uid sum1步骤做的标记，当每个标记都大于等于1，说明这个uid在这段时间出现过

按照需求：sum(7d) a,sum(30d) b,sum(da30) c

当a>0,b=0,c=0 新晋用户

当a>0,b>0 或a>0,c>0 忠实用户

当a=0,b>0 沉睡用户

当a=0,b=0,c>0 流失用户

3.计算，排序

完整代码：

with t as (

select uid,date(in_time),if(date(in_time) >= date_sub((select max(in_time) from tb_user_log),interval 7 day),1,0) as 7d,

if(date(in_time) < date_sub((select max(in_time) from tb_user_log),interval 7 day) and date(in_time) >= date_sub((select max(in_time) from tb_user_log),interval 30 day),1,0) as 30d,

if(date(in_time) < date_sub((select max(in_time) from tb_user_log),interval 30 day),1,0) as ls

from tb_user_log a

order by uid desc

),

t1 as (

select uid,sum(7d) a,sum(30d) b,sum(ls) c from t group by uid

),

t2 as (

select uid,a,b,c,

case when a>0 and b=0 and c=0 then '新晋用户'

when (a>0 and b>0 ) or (a>0 and c>0) then '忠实用户'

when a=0 and b>0 then '沉睡用户'

when a=0 and b=0 and c>0 then '流失用户' end as lx from t1

)

select lx,

round(count(1)/(select count(distinct uid) from tb_user_log),2) rate

from t2 group by lx

order by rate desc