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

select
    t1.video_id,
    round(
        sum(
            case 
                when timestampdiff(second,start_time,end_time) >= duration then 1
                else 0
            end
        )/count(t1.id),3
    ) avg_comp_play_rate
from tb_video_info t1
inner join (
    select id,video_id,start_time,end_time
    from tb_user_video_log
    where year(start_time) = 2021
) t2
on t1.video_id = t2.video_id
group by video_id
order by avg_comp_play_rate desc;