用户等级如何划分？ | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
http://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
这道题，用户等级这几个区间段划分给我整吐了~~~把现有的讨论和解题区翻遍了，也没找到我认可的简洁方法。主要是不少同学在取时间间隔的时要么以今天和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;
