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

WITH t AS (
  SELECT
    t1.video_id,
    (
      unix_timestamp(end_time) - unix_timestamp(start_time)
    ) as diff_second,
    t2.duration
  FROM
    tb_user_video_log AS t1
    LEFT JOIN tb_video_info AS t2 ON t1.video_id = t2.video_id
  WHERE
    datediff(t1.start_time, '2021-01-01') >= 0
    AND datediff(t1.end_time, '2021-01-01') >= 0
)
SELECT
  t.video_id,
  round(
    sum(if(t.diff_second - t.duration < 0, 0, 1)) / count(1),
    3
  ) AS avg_comp_play_rate
FROM
  t
GROUP BY
  t.video_id
ORDER BY
  avg_comp_play_rate desc;

看题仔细点，小条件太多了