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

# 为了省空间多了一些步骤，不过更加清晰了
SELECT
    tag,
    CONCAT(avg, '%') AS avg_play_progress #先把大于60条件完成以后再使用concat合并
FROM(
    SELECT
        tag,
        ROUND(AVG(IF(play_percent > 100, 100, play_percent)), 2) AS avg #2.计算各类tag平均播放进度，注意条件
    FROM(
        SELECT
            tag,
            duration,
            100 * TIMESTAMPDIFF(SECOND, start_time, end_time) / duration AS play_percent #1.计算播放进度
        FROM tb_user_video_log tu
        JOIN tb_video_info tv
        ON tu.video_id = tv.video_id
    ) AS tb1
    GROUP BY tag
    HAVING avg > 60 #条件平均进度大于60
) AS tb2
ORDER BY avg_play_progress DESC #最后条件播放进度倒序排序