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

统计活跃间隔对用户分级结果

https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af

#比较好理解的解法
with tiaojian as (
select
uid,
min(date(in_time))over(partition by uid order by date(in_time)) as first_date,
max(date(in_time))over(partition by uid order by date(in_time)) as max_date,
lag(date(in_time),1)over(partition by uid order by date(in_time) asc) as last_date,
max(date(in_time))over() as now_date,
row_number()over(partition by uid order by date(in_time) desc) as m 
from tb_user_log
)

select 
user_grade,
round(
count(uid)/pt,2) as ratio
from(
select 
uid,
case 
when datediff(now_date,max_date)<=6 and datediff(now_date,first_date)>6 then "忠实用户"
when  datediff(now_date,first_date)<=6 then "新晋用户"
when  datediff(now_date,max_date) between 7 and 29 then "沉睡用户"
when  datediff(now_date,max_date)>29 then "流失用户" end as user_grade,
count(uid)over()pt
from tiaojian 
where
m=1
) as t 
group by user_grade,pt
order by ratio desc,user_grade

全部评论

相关推荐

榕城小榕树:1200单休,我去干点啥别的不好
点赞 评论 收藏
分享
MinJerous:虽然我一直说 计算机不怎么卡学历 但是至少得一本
点赞 评论 收藏
分享
05-26 10:24
门头沟学院 Java
qq乃乃好喝到咩噗茶:其实是对的,线上面试容易被人当野怪刷了
找工作时遇到的神仙HR
点赞 评论 收藏
分享
评论
1
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务