题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

本题目考查主要有3点：

1. 日期的转换，表中日期的格式是y-m-d-s ,由于题目要求以y-m的格式输出，所以要转换日期格式，这里提供两种日期转换格式的思路，（1）substr(start_time,1,7)) （2）date_format(start_time,'%Y-%m')

2. 求粉丝数量，if_follow=1为增加粉丝、if_follow=2为减少粉丝，使用sum(case when..then..else..end）

3. 求月累计粉丝数量，窗口函数，sum(...)partition by author order by substr(start_time,1,7)

      select 
              author,
              substr(start_time,1,7) month,
                
              round(sum(case when if_follow=1 then 1 
      when if_follow=2 then -1 else 0 end)/ count(author),3) r1,
                
            sum(sum(case when if_follow=1 then 1 when if_follow=2 then -1
     else 0 end))over(partition by author order by                         substr(start_time,1,7))total1  
              
from tb_user_video_log a

join tb_video_info b on a.video_id=b.video_id

where year(start_time)=2021

group by author,month

order by author,total1