题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
在小红书看到一种解法，非常易懂
step 1: 先找到当前时间：-- date((select max(in_time) from tb_user_log))指的是当前时间
step 2:用case when对四种用户类型进行分层
case when datediff(date((select max(in_time) from tb_user_log)) ,date(min(in_time)))<=7 then '新晋用户' -- 这里的date(min(in_time)指的是这个新用户第一次一次登陆的时候
when datediff(date((select max(in_time) from tb_user_log)) ,date(max(in_time))) between 7 and 29 then '沉睡用户' -- 这里的date(max(in_time)指的是这个老用户最后一次登陆的时候
when datediff(date((select max(in_time) from tb_user_log)) ,date(max(in_time)))>=30 then '流失用户' -- 这里的date(max(in_time)指的是这个老用户最后一次登陆的时候
when datediff(date((select max(in_time) from tb_user_log)) ,date(max(in_time)))<=7 and -- 指的是用户最后一次登陆在七天之内
datediff(date((select max(in_time) from tb_user_log)) ,date(min(in_time)))>=7 then '忠实用户' -- (指的是用户从第一次登陆到当前时间当于等于7天，就是忠实用户
step 3: 然后用case when 的结果 user_level除以总用户就行：select res.user_level, round(count(uid)/(select count(distinct uid) from tb_user_log),2) as rate
完整代码如下：
select res.user_level, round(count(uid)/(select count(distinct uid) from tb_user_log),2) as rate
from(select uid,
case when datediff(date((select max(in_time) from tb_user_log)) ,date(min(in_time)))<=7 then '新晋用户'
when datediff(date((select max(in_time) from tb_user_log)) ,date(max(in_time))) between 7 and 29 then '沉睡用户'
when datediff(date((select max(in_time) from tb_user_log)) ,date(max(in_time)))>=30 then '流失用户'
when datediff(date((select max(in_time) from tb_user_log)) ,date(max(in_time)))<=7 and
datediff(date((select max(in_time) from tb_user_log)) ,date(min(in_time)))>=7 then '忠实用户'
else null
end as user_level
from tb_user_log
group by 1)as res
group by 1
order by rate desc;
查看7道真题和解析