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

select 
b.author,
date_format(a.start_time,'%Y-%m') as month,
round(sum(case when if_follow = 2 then -1 else if_follow end)/count(uid),3) as fans_growth_rate,
sum(sum(case when if_follow = 2 then -1 else if_follow end)) over(partition by b.author order by date_format(a.start_time,'%Y-%m')) as total_fans
from
tb_user_video_log a
left join
tb_video_info b
on a.video_id = b.video_id
where date_format(a.start_time,'%Y') = '2021'
group by b.author,date_format(a.start_time,'%Y-%m')
order by 1,4

感觉自己太菜了，连窗口函数都不知道，但练习了有所提高