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

-- 2021年有播放记录的每个视频的完播率
-- 完播率=完成播放次数/总播放次数
select 
a.video_id 
,round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0))/count(1),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 

还是审题不仔细，不够认真。。。。。