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

明确题意：

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量，涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序

问题分解：

• 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)；
• 筛选2021年的记录：where YEAR(start_time) = 2021
• 计算每个创作者每个月的涨粉量和播放量：
  • 按创作者和月份分组：group by author, DATE_FORMAT(start_time, "%Y-%m")
  • 计算涨粉量（特殊情况if_follow=2时表示掉粉）：sum(if(if_follow=2, -1, if_follow)) as fans_add_cnt
  • 计算播放量（每条记录就是一次播放）：count(1) as play_cnt
• 计算每个创作者每个月的涨粉量和播放量：
  • 计算涨粉率，保留3位小数：round(fans_add_cnt / play_cnt, 3)
  • 计算截止当月的总粉丝量（累积求和）：sum(fans_add_cnt) over(partition by author order by month)

细节问题：

• 表头重命名：as

完整代码：

select author, `month`, round(fans_add_cnt / play_cnt, 3) as fans_growth_rate,
    sum(fans_add_cnt) over(partition by author order by `month`) as total_fans
from (
    select author,
        DATE_FORMAT(start_time, "%Y-%m") as `month`,
        sum(if(if_follow=2, -1, if_follow)) as fans_add_cnt,
        count(1) as play_cnt
    from tb_user_video_log
    join tb_video_info USING(video_id)
    where YEAR(start_time) = 2021
    group by author, `month`
) as t_author_monthly_fans_play_cnt
order by author, total_fans;