题解 | #平均活跃天数和月活人数#

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')) as total_fans
FROM tb_user_video_log log 
left join tb_video_info info on log.video_id=info.video_id
where year(start_time)=2021
group by author,month
order by author,total_fans  

好难啊