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

# 查询2021年里每位创作者每月的涨粉率及截止当月的总粉丝量
select author, date_format(start_time,'%Y-%m') `month`,
round((sum(if_follow=1)-sum(if_follow=2))/count(*),3) fans_growth_rate,
sum(sum(if_follow=1)-sum(if_follow=2))over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
from tb_user_video_log tl
right join tb_video_info ti
on tl.video_id=ti.video_id
where year(start_time) = '2021'
group by author, date_format(start_time,'%Y-%m')
order by author, total_fans;