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

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