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

with s1 as(select author,left(start_time,7) month
,round(sum(case when if_follow = 2 then -1 when if_follow = 1 then 1 else 0 end)/count(*),3) fans_growth_rate
,sum(case when if_follow = 2 then -1 when if_follow = 1 then 1 else 0 end) sum_fans
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
where year(start_time) = '2021'
group by author,left(start_time,7))
select author,month,fans_growth_rate
,sum(sum_fans)over(partition by author order by month) total_fans
from s1
order by author,total_fans;