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

select author
,date_format(start_time,'%Y-%m') month
,round(sum(case when if_follow=1 then 1
             when if_follow=2 then -1
             else 0 end)/count(author),3)
,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'))as z
from tb_user_video_log a
left join tb_video_info b
on a.video_id=b.video_id
where year(start_time)=2021
group by author,month
order by author,z