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

select log.video_id,
round(
       sum(CASE WHEN log.end_time-log.start_time>=info.duration THEN 1 ELSE 0 END)
      /count(log.video_id),3) as avg_comp_play_rate

from tb_user_video_log as log 
	left outer join tb_video_info as info
    on log.video_id=info.video_id

where year(log.start_time)=2021
group by log.video_id
order by avg_comp_play_rate desc