题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
每个创作者每月的涨粉率及截止当前的总粉丝量
http://www.nowcoder.com/practice/d337c95650f640cca29c85201aecff84
select author,
date_format(start_time,'%Y-%m') as month,
round(sum(case when if_follow=1 then 1 when if_follow=2 then -1 else 0 end)/(count(tb_user_video_log.video_id)),3) as 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 join tb_video_info on tb_user_video_log.video_id=tb_video_info.video_id
where year(start_time)=2021
group by author, date_format(start_time,'%Y-%m')
order by author, total_fans asc
注释
- 日期函数：date_format(date,format)
- 条件判断函数：case when
- 数学函数：round（x，y）
- 窗口函数 sum()over()
