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

select video_id ,ROUND(AVG(if(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0)),3) AS avg_comp_play_rate
from tb_user_video_log
JOIN tb_video_info USING(video_id)
where year(start_time)=2021
group by video_id
ORDER BY avg_comp_play_rate DESC