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

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