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

WITH bofang AS (
    SELECT 
        video_id,
        TIMESTAMPDIFF(SECOND, start_time, end_time) AS "view_time"
    FROM tb_user_video_log
)
SELECT
    t2.tag,
    CONCAT(t2.avg_play_progress, "%") AS avg_play_progress
FROM (
    SELECT
        t.tag,
        ROUND(AVG(t.jindu)*100, 2) AS avg_play_progress
    FROM (
        SELECT 
            tag,
            CASE WHEN view_time >= duration THEN 1
            ELSE view_time/duration
            END AS jindu
        FROM tb_video_info 
        JOIN bofang ON tb_video_info.video_id = bofang.video_id) t
    GROUP BY t.tag
    HAVING avg_play_progress > 60
    ORDER BY avg_play_progress DESC) t2