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

#完播率 round(sum(if(end_time-start_time>=duration,1,0))/count(start_time),3) as avg_comp_play_rate  



select a.video_id,
 round(sum(if(end_time-start_time>=duration,1,0))/count(start_time),3) as avg_comp_play_rate  #完播率
from tb_user_video_log a
inner join tb_video_info b
on a.video_id=b.video_id
where year(start_time)=2021 #日期 
group by a.video_id
order by avg_comp_play_rate desc;