select video_id, round( ifnull( sum( case when view_time >= duration then 1 else 0 end ) / count(*), 0 ), 3 ) as avg_comp_play_rate from ( select tvi.video_id, duration, TIMESTAMPDIFF(second, start_time, end_time) as view_time from tb_user_video_log as tuvl right join tb_video_info as tvi using (...
