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

select
  tv.author author,
  date_format(tu.start_time, '%Y-%m') month,
  round(
    sum(
      case
        when if_follow = 1 then 1
        when if_follow = 2 then -1
        else 0
      end
    ) / count(tu.start_time),
    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')
  ) total_fans
from
  tb_user_video_log tu
  left join tb_video_info tv on tu.video_id = tv.video_id
where
  year(tu.start_time) = 2021
group by
  tv.author,
  month
order by
  tv.author,
  total_fans