题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
https://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
with t as ( select author, tb_video_info.video_id, date_format(start_time,"%Y-%m") as month, case when if_follow = 2 then -1 else if_follow end as if_follow from tb_video_info left join tb_user_video_log on tb_user_video_log.video_id = tb_video_info.video_id where year(start_time)=2021 ) select author, month, # 1. 2021年里每个创作者每月的涨粉率 round(sum(if_follow)/count(if_follow),3) as fans_growth_rate, # 2. 2021年里每个创作者截止当月的总粉丝量 sum(sum(if_follow)) over(partition by author order by month) as total_fans from t group by author, month order by author, total_fans
每个创作者截止当月的总粉丝量:按时间累加每个创作者每月的总粉丝数
即 ( sum(每个创作者每月的总粉丝数 ) over(partition by 创作者 order by month))
每个创作者每月的总粉丝数 = sum(if_follow) group by author, month
每个创作者截止当月的总粉丝量 = sum(每个创作者每月的总粉丝数) over(partition by author order by month)
= sum(sum(if_follow)) over(partition by author order by month) group by author, month