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

感觉观点不是很对，如果过用户是近7天内的新用户，并且在7天内不止活跃1次！用这个方法就没办法确定用户是“忠实”还是“新晋”！我觉得应该加个条件，判断用户首次活跃的时间在不在7天内，以此来确定是“忠实”还是“新晋” select user_grade,round(count(*)/(select count(distinct uid) from tb_user_log),2) as ratio from (select uid, (case when gap<7 and datediff((select max(in_time) from tb_user_log),start_time)>=7 then '忠实用户' when gap<7 and datediff((select max(in_time) from tb_user_log),start_time)<7 then '新晋用户' when gap>=7 and gap<30 then '沉睡用户' when gap>=30 then '流失用户' end) as user_grade from (select uid,datediff((select max(in_time) from tb_user_log),max(in_time)) as gap, min(date(in_time)) as start_time from tb_user_log group by uid) t1) t2 group by user_grade order by ratio desc;

如果这个新用户七天内登录两次呢 这个times是不是就变成2了？就计算成不是新用户了？不是很懂

牛哇牛哇

思路很好！学习到了

times这里妙啊

大佬，求问timestampdiff(day,in_time,(select max(in_time) from tb_user_log))这个是求的什么呢？为什么后面的max(in_time)要在select里面呢

请问为什么times = 1表示新用户呢 不是很懂 谢谢大佬

你好，count(*) over(partition by uid order by in_time) as times是排名的是总人数？上面max(times)是取人数最多的排名？最后 times!=1以及 times=1是什么意思？

题解有个不严谨的地方就是，如果用户是近7天内的新用户，并且在7天内不止活跃1次，用这个方法就没办法确定用户是“忠实”还是“新晋”,想在原题解的基础上做一下修改，确保题解的严谨性，没想到意外发现times根本就不用去计算，真是没想到能在确保题解严谨性的同时进一步精简代码，具体如下： select grade ,round(count(*)/(select count(distinct uid) from tb_user_log),2) as ration from (select case when min_gap<7 and max_map>=7 then "忠实用户" when min_gap<7 and max_map<7 then "新晋用户" when min_gap>=7 and min_gap<30 then "沉睡用户" when min_gap>=30 then "流失用户" end as grade, uid from(select uid,min(gap) as min_gap,max(gap) as max_map from(select uid,timestampdiff(day,in_time,(select max(in_time) from tb_user_log)) as gap from tb_user_log) tmp group by uid) base) info group by grade order by ration desc,grade