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

-- ③计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
select author,month,fans_growth_rate,
       sum(total_fans)over(partition by author order by month) total_fans
from(
-- ②计算2021年里每个创作者每月的涨粉率及当月的总粉丝量
    select  author,month,
            round(sum(total_fans)/sum(cnt),3) fans_growth_rate,
            sum(total_fans) total_fans
    from
    (
-- ①先计算2021年里每个视频每月的播放次数和粉丝数
        select video_id,
        date_format(start_time,'%Y-%m') month,
        count(1) cnt,
        sum(if(if_follow=2,-1,if_follow)) total_fans
        from tb_user_video_log
        where year(start_time) = '2021'
        group by video_id,date_format(start_time,'%Y-%m') 
    ) t1
    join tb_video_info t2
    on t1.video_id=t2.video_id
    group by author,month
)t3
order by author,total_fans