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

select
  tv.video_id as video_id,
  round(
    avg(
      case
        when (tu.end_time - tu.start_time) >= tv.duration then 1
        else 0
      end
    ),
    3
  ) as avg_comp_play_rate
from
  tb_user_video_log tu
  inner join tb_video_info tv on tu.video_id = tv.video_id
where 
    year(tu.start_time) = '2021'
group by
  tv.video_id
order by
  avg_comp_play_rate desc