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

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

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

with T1_table as ( 
select "忠实用户" as user_grade, round(T1_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
), T2_table as (
select "新晋用户" as user_grade, round(T2_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
), T3_table as (
select "沉睡用户" as user_grade, round(T3_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
), T4_table as (
select "流失用户" as user_grade, round(T4_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
)

select * from T1_table
union all
select * from T2_table
union all
select * from T3_table
union all
select * from T4_table
order by ratio desc

全部评论

相关推荐

不愿透露姓名的神秘牛友
今天 12:23
点赞 评论 收藏
分享
05-29 20:34
门头沟学院 C++
KarlAllen:得做好直接春招的准备。学历差的话,一是面试要求会比学历好的严格不少,二是就算面试通过了也会被排序。总之暑期和秋招对于学历差的就是及其不友好
无实习如何秋招上岸
点赞 评论 收藏
分享
06-20 17:42
东华大学 Java
凉风落木楚山秋:要是在2015,你这简历还可以月入十万,可惜现在是2025,已经跟不上版本了
我的简历长这样
点赞 评论 收藏
分享
就前几天旅游的时候,打开抖音就经常刷到这类视频:以前是高学历学生、老师、主持人,现在做着团播、擦边主播的工作,以及那些经过精心包装的“职业转型”故事——从铺天盖地的VLOG到所谓的“04年夜场工作日记”,这些内容在初中升学、高考放榜等关键时间节点持续发酵。可以说非常直接且精准地在潜移默化地影响着心智尚未成熟的青少年,使其对特殊行业逐渐脱敏。那我就想问了:某些传播公司、平台运营者甚至某些夜场的老板,你们究竟在传递怎样的价值观?点开那些视频,评论区里也是呈现明显的两极分化:一种是​​经济下行论​​:“现在就业市场已经艰难到这种程度了吗?”​​一种是事实反驳派​​:这些创作者往往拥有名校背景,从事着...
牛客刘北:被环境教育的,为了能拿到足够的钱养活自己,不甘心也得甘心,现在的短视频传播的思想的确很扭曲,但是很明显,互联网玩上一年你就能全款提A6,但你全心全意不吃不喝工作一年未必能提A6,但是在高考中考出现这个的确很扭曲,在向大家传播“不上学,玩互联网也可以轻松年入百万”,不是人变了,是社会在变
预测一下26届秋招形势
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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