题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
http://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
#总体思路：给每个日期加上标签
- 怎么就不会转换一下思维呢？只要把比较的日期变成两个日期减出来的数字就好了啊！
- 在时间函数里不能直接出现select语句，但是可以用date函数把select语句包起来
- DATE((SELECT MAX(in_time) FROM tb_user_log))不可以单独成表然后引用，因为命令最后的group函数是作用到两个表格里的，如果计算时间的表格没有uid就会报错
select user_grade,
round(count(*)/(select count(distinct uid) from tb_user_log),2) ratio
from (
select uid,
(case when timestampdiff(day,max(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))<=6
and timestampdiff(day,min(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))>6 then '忠实用户'
when timestampdiff(day,max(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log))) between 6 and 29 then '沉睡用户'
when timestampdiff(day,max(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))>29 then '流失用户'
when timestampdiff(day,min(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))<6 then '新晋用户'
end) user_grade
from tb_user_log
group by uid
)t
group by user_grade
order by ratio desc
round(count(*)/(select count(distinct uid) from tb_user_log),2) ratio
from (
select uid,
(case when timestampdiff(day,max(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))<=6
and timestampdiff(day,min(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))>6 then '忠实用户'
when timestampdiff(day,max(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log))) between 6 and 29 then '沉睡用户'
when timestampdiff(day,max(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))>29 then '流失用户'
when timestampdiff(day,min(date(in_time)),DATE((SELECT MAX(in_time) FROM tb_user_log)))<6 then '新晋用户'
end) user_grade
from tb_user_log
group by uid
)t
group by user_grade
order by ratio desc
投递牛客等公司10个岗位 >
查看14道真题和解析