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

SELECT tag, CONCAT(ROUND(100*avg(ratio),2),'%') avg_play_progress
FROM(
SELECT a.uid, a.video_id, b.tag, 
    CASE WHEN a.tt>=b.duration THEN 1
    ELSE a.tt/b.duration END ratio
FROM
(SELECT uid, video_id, 
    UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) tt
FROM tb_user_video_log
) a 
LEFT JOIN 
(SELECT video_id, tag, duration
FROM tb_video_info
GROUP BY video_id, tag, duration
) b 
on a.video_id=b.video_id
) m1 
GROUP by tag
having avg(ratio)>0.6
ORDER by avg(ratio) DESC