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

with T0 as
(select T1.uid, T1.video_id, timestampdiff(second,start_time,end_time) diff, T2.duration
from tb_user_video_log T1
left join tb_video_info T2
on T1.video_id = T2.video_id
where year(T1.start_time) = 2021
)

select video_id, round(avg(if(duration> diff, 0, 1)),3) avg_comp_play_rate
from T0
group by video_id
order by avg_comp_play_rate desc