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

SELECT
  tag,
  CONCAT(avg_play_progress, '%') avg_play_progress
FROM
  (
    SELECT
      tag,
      ROUND(
        AVG(
          IF(
            (
              TIMESTAMPDIFF(SECOND, start_time, end_time) > duration
            ),
            1,
            (
              TIMESTAMPDIFF(SECOND, start_time, end_time) / duration
            )
          ) * 100
        ),
        2
      ) avg_play_progress
    FROM
      tb_user_video_log tuvl
      INNER JOIN tb_video_info tvi USING(video_id)
    GROUP BY
      tag
    HAVING
      avg_play_progress > 60.00
    ORDER BY
      avg_play_progress DESC
  ) tb