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

select
    video_id,
    round(
        count(
            case
                when end_time - start_time >= duration then video_id
                else null
            end
        ) / count(video_id),
        3
    ) as avg_comp_play_rate
from
    (
        select
            a.video_id,
            a.end_time, 
            a.start_time,
            b.duration
        from
            tb_user_video_log as a
            left join tb_video_info as b on a.video_id = b.video_id
    ) as t
where year(start_time) = '2021'
group by
    video_id
order by
    avg_comp_play_rate desc