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

select
    *
from
    (
        select
            author,
            month,
            fans_growth_rate,
            sum(fans) over (
                partition by
                    author
                order by
                    month
            ) as total_fans
        from
            (
                select
                    author,
                    month,
                    round(
                        sum(
                            case
                                when if_follow = 1 then 1
                                when if_follow = 2 then -1
                                else 0
                            end
                        ) / count(1),
                        3
                    ) as fans_growth_rate,
                    sum(
                        case
                            when if_follow = 1 then 1
                            when if_follow = 2 then -1
                            else 0
                        end
                    ) as fans
                from
                    (
                        select
                            video_id,
                            date_format (start_time, '%Y-%m') as month,
                            if_follow,
                            author
                        from
                            tb_user_video_log t1
                            inner join tb_video_info t2 using (video_id)
                    ) t3
                group by
                    author,
                    month
            ) t4
    ) t5
where
    total_fans > 0
order by
    author,
    total_fans