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

(select
  tv.author,
  DATE_FORMAT(tu.start_time, "%Y-%m") month,
  round(
    sum(
      case
        when if_follow = 2 then -1
        else if_follow
      end
    ) / count(1),
    3
  ) fans_growth_rate,
  SUM(
	sum(
    case
      when if_follow = 2 then -1
      else if_follow
    end
  )) OVER(PARTITION BY tv.author ORDER BY DATE_FORMAT(tu.start_time, "%Y-%m")) total_fans
from
  tb_video_info tv
  inner join tb_user_video_log tu on tv.video_id = tu.video_id
	WHERE YEAR(tu.start_time) = 2021
group by
  DATE_FORMAT(tu.start_time, "%Y-%m"),
  tv.author
order by
  tv.author,
  total_fans)