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

SELECT

author,

MONTH ( start_time ) mon,

sum( CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN - 1 ELSE 0 END ) / count(*),

sum(

sum( CASE WHEN if_follow = 1 THEN 1 WHEN if_follow = 2 THEN - 1 ELSE 0 END )) over (

PARTITION BY author

ORDER BY

MONTH ( start_time ))

FROM

tb_user_video_log a

JOIN tb_video_info b ON a.video_id = b.video_id

GROUP BY

author,

MONTH (

start_time)