题解 | 平均播放进度大于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_progress;