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

select 
    distinct t2.video_id,
    round(avg(if (timestampdiff(second,start_time,end_time) >= duration, 1, 0)) ,3)as avg_comp_play_rate
from
    tb_video_info t1
    inner join tb_user_video_log t2 on t1.video_id = t2.video_id
where YEAR(start_time)='2021' and YEAR(end_time)='2021'
group by
    t2.video_id
order by avg_comp_play_rate desc