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

SELECT
    T.tag,
    CONCAT (T.avg_play_progress, '%')
FROM
    (
        SELECT
            T2.tag,
            FORMAT (
                AVG(
                    if (T1.BTime > T2.duration, 1, T1.BTime / T2.duration)
                ) * 100,
                2
            ) AS avg_play_progress
        FROM
            (
                SELECT
                    video_id,
                    time_to_sec (timediff (end_time, start_time)) AS BTime
                FROM
                    tb_user_video_log
            ) AS T1
            NATURAL JOIN tb_video_info AS T2
        GROUP BY
            T2.tag
        having
            avg_play_progress > 60
        ORDER BY
            avg_play_progress DESC
    ) AS T;