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

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

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

全部评论

相关推荐

不愿透露姓名的神秘牛友
07-03 18:13
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
07-04 18:06
点赞 评论 收藏
分享
陆续:不可思议 竟然没那就话 那就我来吧 :你是我在牛客见到的最美的女孩
点赞 评论 收藏
分享
06-26 22:20
门头沟学院 Java
码农索隆:让你把简历发给她,她说一些套话,然后让你加一个人,说这个人给你改简历,然后开始卖课
我的求职精神状态
点赞 评论 收藏
分享
评论
1
收藏
分享

创作者周榜

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