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

select
    uvl.video_id,
    round(
        avg(
            case 
                when timestampdiff(second, uvl.start_time, uvl.end_time) >= vi.duration then 1
                else 0
            end
        ), 
        3
    ) avg_comp_play_rate
from
    tb_user_video_log uvl
    inner join tb_video_info vi on uvl.video_id = vi.video_id
where
    date_format(start_time, '%Y') = 2021
    and date_format(end_time, '%Y') = 2021
group by 
    uvl.video_id
order by 
    avg_comp_play_rate desc