用户等级如何划分？ | #统计活跃间隔对用户分级结果#

这道题，用户等级这几个区间段划分给我整吐了~~~把现有的讨论和解题区翻遍了，也没找到我认可的简洁方法。主要是不少同学在取时间间隔的时要么以今天和in_time或者out_time的最小值最大值为基准，我觉得不妥，因为可能出现min_time和out_time跨天且卡着划分标准的情况。所以时间间隔还是要分别以in_time和out_time划分

1、计算出每个用户的最早活跃日期 min(in_time) 以及 最近活跃日期 max(out_time)

select uid,
date(min(in_time)) first_dt, 
date(max(out_time)) last_dt,
from tb_user_log
GROUP BY uid;

2、以 时间间隔 为依据确定用户等级，那重点就是围绕datediff(结束时间，开始时间)这俩时间。

• 结束时间 题目说了是最大日期，即为所有数据的date(max(out_time))
• 开始时间 究竟以in_time还是以out_time计算呢，我的理解是都计算，这是难点！！！！我在此卡克了好久

最终，我按照以下标准划分等级（得细品，得细品，得细品ε=(´ο｀*)))，对照输出结果反向理解会好一点）

case
when datediff(t.today,t.first_dt)<7 then '新晋用户' #近7天新增
when datediff(t.today,t.last_dt)<7 then '忠实用户'  #近7天活跃过且非新晋用户
when datediff(t.today,t.last_dt)>=30 then '流失用户' #近30天未活跃但更早前活跃过
when 7<=datediff(t.today,t.last_dt)<30 then '沉睡用户'#近7天未活跃但更早前活跃过
end

已知各用户的最早日期，最近日期，和今日日期，不难计算datediff，由datediff就可使用case when 划分grade。上代码

select *,
datediff(t.today,t.first_dt) dif1, 
datediff(t.today,t.last_dt) dif2,
case
when datediff(t.today,t.first_dt)<7 then '新晋用户'
when datediff(t.today,t.last_dt)<7 then '忠实用户'
when datediff(t.today,t.last_dt)>=30 then '流失用户'
when 7<=datediff(t.today,t.last_dt)<30 then '沉睡用户'
end user_grade
FROM
(
select uid,
date(min(in_time)) first_dt, 
date(max(out_time)) last_dt,
(select date(max(out_time)) from tb_user_log) today
from tb_user_log
GROUP BY uid
)t;

3、以grade进行分组，计算出各等级用户数。用户数/用户总数则是ratio

综上，完整代码如下

select t1.user_grade,
round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio FROM
(  
select *,
datediff(t.today,t.first_dt) dif1, 
datediff(t.today,t.last_dt) dif2,
case
when datediff(t.today,t.first_dt)<7 then '新晋用户'
when datediff(t.today,t.last_dt)<7 then '忠实用户'
when datediff(t.today,t.last_dt)>=30 then '流失用户'
when 7<=datediff(t.today,t.last_dt)<30 then '沉睡用户'
end user_grade
FROM
(
select uid,
date(min(in_time)) first_dt, 
date(max(out_time)) last_dt,
(select date(max(out_time)) from tb_user_log) today
from tb_user_log
GROUP BY uid
)t
)t1
group by t1.user_grade
order by ratio desc;