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

with t1 as(
select
       tuvl.video_id,
       timestampdiff(second, start_time,end_time) comp_time,
       duration
from tb_user_video_log tuvl
left join tb_video_info tvi on tuvl.video_id = tvi.video_id
where year(start_time)=2021
)
select
       video_id,
       round(count(if(comp_time>=duration,1,null))/count(1),3) avg_comp_play_rate
from t1
group by video_id
order by avg_comp_play_rate desc