题解 | #平均播放进度大于60%的视频类别#

SELECT
    author,
    SUBSTR(start_time,1,7) AS month,
    ROUND(SUM(CASE
    WHEN if_follow=2 THEN -1
    WHEN if_follow=0 THEN 0
    WHEN if_follow=1 THEN 1
    END )/COUNT(video_id),3) AS fans_growth_rate,
    SUM(
    SUM(CASE
    WHEN if_follow=2 THEN -1
    WHEN if_follow=0 THEN 0
    WHEN if_follow=1 THEN 1
    END)) OVER(PARTITION BY author ORDER BY SUBSTR(start_time,1,7)) AS total_fans
FROM tb_video_info info
JOIN tb_user_video_log log USING(video_id)
WHERE YEAR(end_time)=2021
GROUP BY author,SUBSTR(start_time,1,7)
ORDER BY author,total_fans