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

select
    a.video_id,
    round(sum(case when 
                        UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time) >= b.duration 
                    then 1 else 0 end) / count(a.video_id), 3) avg_comp_play_rate
from tb_user_video_log a
inner join tb_video_info b on a.video_id = b.video_id
WHERE year(start_time) = 2021
group by a.video_id
order by avg_comp_play_rate desc;