逻辑清晰

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

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


with t as 
(
    select uid,max(date(in_time))over(partition by uid) as max_part_time,
               min(date(in_time))over(partition by uid) as min_part_time,
               date_add(max(date(out_time))over(),interval -6 day) as 7_time,
               date_add(max(date(out_time))over(),interval -29 day) as 29_time,
               (select count(distinct uid) from tb_user_log) as total_num
    from tb_user_log
)
select user_grade,round(count(1)/total_num,2) as ratio
from 
    (select distinct uid ,'忠实用户' as user_grade,total_num
    from t
    where min_part_time<7_time and max_part_time>=7_time
    union ALL
    select distinct uid,'新晋用户' as user_grade,total_num
    from t
    where min_part_time>=7_time
    union all
    select distinct uid,'沉睡用户' as user_grade,total_num
    from t 
    where max_part_time<7_time and max_part_time >=29_time
    union ALL
    select distinct uid,'流失用户' as user_grade,total_num
    from t
    where max_part_time<29_time
    ) t1
group by user_grade
order by ratio desc

全部评论

相关推荐

05-21 15:47
门头沟学院 Java
浪漫主义的虹夏:项目有亮点吗,第一个不是纯玩具项目吗,项目亮点里类似ThreadLocal,Redis储存说难听点是花几十分钟绝大部分人都能学会,第二个轮子项目也没体现出设计和技术,想实习先沉淀,好高骛远的自嗨只会害了自己
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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