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

select t2.author
, substr(end_time,1,7) as month
, round(sum(if(if_follow=1,1,if(if_follow=2,-1,0)))/count(start_time),3)
, sum(sum(if(if_follow=1,1,if(if_follow=2,-1,0)))) over (partition by author order by substr(end_time,1,7)) as total_fan
from tb_user_video_log
join tb_video_info as t2 using (video_id)
where year(end_time) = 2021
group by 1,2
order by 1,4