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

WITH play_info AS(
    SELECT
        u.video_id,
        TIMESTAMPDIFF(SECOND, start_time, end_time) AS play_time,
        v.tag,
        v.duration,
        CASE 
            WHEN TIMESTAMPDIFF(SECOND, start_time, end_time) / v.duration < 1 
            THEN TIMESTAMPDIFF(SECOND, start_time, end_time) / v.duration 
            ELSE 1 
        END AS play_progress
    FROM tb_user_video_log u
    LEFT JOIN tb_video_info v
    ON u.video_id = v.video_id
)

SELECT 
    tag,
    CONCAT(ROUND(AVG(play_progress * 100), 2), "%") AS avg_play_progress
FROM play_info
GROUP BY tag
HAVING AVG(play_progress) > 0.6
ORDER BY avg_play_progress DESC