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

SELECT
    tag,
    CONCAT (avg_play_progress, "%") AS avg_play_progress
FROM
    (
        SELECT
            tag,
            ROUND(
                AVG(
                    IF (
                        TIMESTAMPDIFF (SECOND, start_time, end_time) > duration,
                        100,
                        (
                            TIMESTAMPDIFF (SECOND, start_time, end_time) / duration
                        ) * 100
                    )
                ),
                2
            ) AS avg_play_progress
        FROM
            tb_user_video_log
            LEFT JOIN tb_video_info ON tb_user_video_log.video_id = tb_video_info.video_id
        GROUP BY
            tag
    ) AS t_progress
WHERE
    avg_play_progress > 60
ORDER BY
    avg_play_progress DESC;