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

select
a.video_id video_i
,round(a.fin/a.total,3) 	avg_comp_play_rate
from
(
select 
v.video_id video_id
,count(*) total
,count(if((timestampdiff(second,u.start_time,u.end_time) >= v.duration),1,null)) fin
from tb_user_video_log u
left join tb_video_info v
on u.video_id = v.video_id
where substring(u.start_time,1,4) = '2021'
group by v.video_id
) a
order by avg_comp_play_rate desc