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

select
    video_id,
    round(
        ifnull(
            sum(
                case
                    when view_time >= duration then 1
                    else 0
                end
            ) / count(*),
            0
        ),
        3
    ) as avg_comp_play_rate
from
    (
        select
            tvi.video_id,
            duration,
            TIMESTAMPDIFF(second, start_time, end_time) as view_time
        from
            tb_user_video_log as tuvl
            right join tb_video_info as tvi using (video_id)
        where
            start_time between '2021-01-01' and '2021-12-31'
            and end_time between '2021-01-01' and '2021-12-31'
    ) temp
group by
    video_id
order by
    avg_comp_play_rate desc