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

select
  video_id,
  round(
    count(
      case
        when F = 'Y' then 1
      end
    ) / count(F),
    3
  ) R
from
  (
    select
      uid,
      lg.video_id,
      if(end_time - start_time >= duration, 'Y', 'N') F
    from
      tb_user_video_log lg
      left join (
        select
          video_id,
          duration
        from
          tb_video_info
      ) d on lg.video_id = d.video_id
    where
      year(start_time) = 2021
  ) b
group by
  video_id
order by
  R desc