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

SELECT author, DATE_FORMAT(start_time,'%Y-%m') as month, FORMAT(SUM(CASE WHEN if_follow = 2 then -1 else if_follow end)/count(*),3) as fans_growth_rate, 
sum(sum(case when if_follow=1 then 1
             when if_follow=2 then -1
             else 0 end)) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
FROM tb_user_video_log a
join tb_video_info b ON a.video_id=b.video_id
WHERE YEAR(start_time)=2021
GROUP BY author, month
ORDER BY author,total_fans