题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量

每个创作者每月的涨粉率及截止当前的总粉丝量

https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84

WITH play_info AS(
    SELECT 
        u.video_id,
        DATE_FORMAT(u.start_time, "%Y-%m") AS month,
        u.if_follow,
        CASE WHEN u.if_follow = 0 THEN 0
            WHEN u.if_follow = 1 THEN 1
            WHEN u.if_follow = 2 THEN -1
        END AS fans,
        v.author
    FROM tb_user_video_log u
    LEFT JOIN tb_video_info v USING (video_id)
    WHERE YEAR(u.start_time) = 2021
),
monthly_fans AS (
    SELECT
        month,
        author,
        SUM(fans) AS month_fans,
        COUNT(*) AS play_times
    FROM play_info
    GROUP BY author, month
)

SELECT 
    author, 
    month,
    ROUND(month_fans / play_times, 3) AS fans_growth_rate,
    SUM(month_fans) OVER (
        PARTITION BY author 
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_fans  -- 截止当月的总粉丝量(累计求和)
FROM monthly_fans
GROUP BY author, month
ORDER BY author, total_fans

这里学到了一个窗口函数的新用法,累计求和:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:从最早月份累加到当前月。
全部评论

相关推荐

点赞 评论 收藏
分享
06-11 17:39
门头沟学院 Java
小呆呆的大鼻涕:卧槽,用户彻底怒了
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
07-24 13:39
在记录秋招的大魔王很...:别被忽悠了,我做了多年销售。我可以告诉你,这就是忽悠你的,销售一定要看底薪也要看提成两者不可缺一。提成是有业绩的时候才拿的到的,谁能保证一直有单状态都好。销售有时候很讲究运气的。底薪是你这个人这个岗位日常工作体现的价值。别小看底薪,你看那些跳槽去做经理主管的,底薪底一些,人家愿意去吗?所以那些说销售靠提成的纯属忽悠,除非他们的业务很容易成单。
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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