题解 | #各个视频的平均完播率#

SELECT
  video_id,
  ROUND(
    SUM(
      IF(
        TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration,
        1,
        0
      )
    ) / COUNT(start_time),
    3
  ) avg_comp_play_rate
FROM
  tb_user_video_log tuvl
  INNER JOIN tb_video_info tvi USING(video_id)
WHERE
  YEAR(tuvl.start_time) = 2021
GROUP BY
  video_id
ORDER BY
  avg_comp_play_rate DESC;