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

SELECT tag, CONCAT(avg_play_progress, "%") as avg_play_progress
FROM (
    SELECT tag,
        ROUND(AVG(
            IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
               TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
        ) * 100, 2) as avg_play_progress
    FROM tb_user_video_log
    JOIN tb_video_info USING(video_id)
    GROUP BY tag
    HAVING avg_play_progress > 60
    ORDER BY avg_play_progress DESC
) as t;