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

select 
tg.video_id,
round(
avg(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end),3) as avg_comp_play_rate
from tb_user_video_log tg inner join tb_video_info tf on 
tg.video_id=tf.video_id
where
year(start_time)=2021
group by tg.video_id
order by avg_comp_play_rate desc