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

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

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

with t1 as
    (
    select uid,
        date_format(in_time, '%Y-%m-%d')          as in_time,
        date_format(max(in_time) over (), '%Y-%m-%d') as today
    from tb_user_log
    ),
t2 as
    (
    select *,
        datediff(today,in_time) as daysub
    from t1
    ),
t3 as
    (
    select * ,
        max(daysub) over (partition by uid) as maxday,
        min(daysub) over (partition by uid) as minday,
        row_number() over (partition by uid) as rn
    from t2
    ),
t5 as
    (
    select uid,
        maxday,
        minday,
        case when maxday <= 6 then 1  -- 新晋
        when maxday > 6 and minday <= 6 then 2 -- 忠实
        when maxday > 6 and minday > 6 and minday <=29 then 3 -- 沉睡
        when maxday > 6 and minday >29  then 5  -- 流失
        else 0 end as usertype
    from t3 where rn = 1
    )
select '忠实用户' as user_grade,round(count(if(usertype = 2,1,null)) / count(1),2) as ratio   from t5
union
select '新晋用户' as user_grade,round(count(if(usertype = 1,1,null)) / count(1),2) as ratio   from t5
union
select '沉睡用户' as user_grade,round(count(if(usertype = 3,1,null)) / count(1),2) as ratio   from t5
union
select '流失用户' as user_grade,round(count(if(usertype = 5,1,null)) / count(1),2) as ratio   from t5
;

全部评论

相关推荐

昨天 11:15
中南大学 Java
好可爱的hr姐姐哈哈哈哈
黑皮白袜臭脚体育生:兄弟们貂蝉在一起,吕布开了
点赞 评论 收藏
分享
认真搞学习:28小登的建议,投算法岗不要写什么物理竞赛,互联网+,多写点项目,用什么算法做了什么。还有本科算法是不可能的开发你这个也没有项目啊
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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