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

select author,imonth,
        round(sum(
            case 
            when if_follow=1 then 1
            when if_follow=2 then -1
            else 0 end
        )/count(*),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 imonth) as total_fans
from
    (select video_id,
            date_format(start_time,'%Y-%m') as imonth,
            if_follow
    from tb_user_video_log
    where year(start_time) = 2021) as a
    left join tb_video_info b using(video_id)
group by author,imonth
order by author,total_fans;