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

SELECT
    tag
    ,CONCAT(ROUND(100*AVG(video_rate),2),'%') AS avg_play_progress
FROM
(
    SELECT
        video_id
        ,min(tag) AS tag
        ,AVG(rate) AS video_rate
    FROM
    (
        SELECT
        t1.video_id
        ,IF(timestampdiff(SECOND,start_time,end_time)/duration>1,1,timestampdiff(SECOND,start_time,end_time)/duration) AS rate
        ,tag
        FROM
        tb_user_video_log AS t1
        LEFT JOIN
        tb_video_info AS t2
        ON t1.video_id=t2.video_id
    )AS t1
    GROUP BY
        video_id
)AS t1
GROUP BY
    tag
HAVING
    avg_play_progress>'60.00%'
ORDER BY
    avg_play_progress desc