题解 | #平均活跃天数和月活人数#
每个创作者每月的涨粉率及截止当前的总粉丝量
http://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
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
好难啊