题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
case when then计算涨粉量,where 语句筛选出2021年信息
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) fans_growth_rate, 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')) total_fans from tb_user_video_log tu left join tb_video_info tv on tu.video_id=tv.video_id where year(start_time)=2021 group by author,month order by author,total_fans;