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

#构建每个作者每月涨粉人数、播放率的表格
with t1 as(
select author,
date_format(end_time,'%Y-%m') as month,
sum(case when if_follow = 0 then 0 when if_follow = 1 then 1 else -1 end) as fans_num,
count(author) as watch_num
from tb_user_video_log a left join tb_video_info b using(video_id)
where year(end_time) = '2021'
group by author, month)
select author, month, 
round(fans_num/watch_num,3) as fans_growth_rate,
sum(fans_num) over(partition by author order by month) as total_fans
from t1
order by author, total_fans