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

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

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

with today as
(select max(out_time) as t from tb_user_log),
lastday as
(select *
from 
(
select *, 
		if(min(in_time) over (partition by uid) = in_time,1,0) as new_act
from tb_user_log
) q
where (uid,in_time) in (select uid,max(in_time) 
						from tb_user_log
						group by uid)
)
select user_grade,
		round(count(user_grade) /sum(count(user_grade)) over (),2) as ratio				
from
(
select lastday.*,
         case
		 when out_time>= date_sub(today.t,interval 6 day) and out_time <= today.t  and new_act = 0 then '忠实用户' 
		 when out_time>= date_sub(today.t,interval 6 day) and out_time <= today.t  and new_act = 1 then '新晋用户' 
		 when out_time< date_sub(today.t,interval 6 day)  and out_time>= date_sub(today.t,interval 29 day) then '沉睡用户' 
		 when out_time< date_sub(today.t,interval 29 day) then '流失用户' 
		 end as user_grade
from lastday,today
) t
group by user_grade
order by ratio desc;
全部评论

相关推荐

小红书 后端开发 总包n+8w+期权
点赞 评论 收藏
分享
喜欢走神的孤勇者练习时长两年半:爱华,信华,等华,黑华
点赞 评论 收藏
分享
评论
1
收藏
分享
牛客网
牛客企业服务