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

select video_id,
    round(完播次数/播放次数,3) avg_comp_play_rate
from (
    select a.video_id,
        count(if(watch_time>=duration,id,null)) 完播次数,
        count(id) 播放次数
    from (select id,video_id,
            timestampdiff(second,start_time,end_time) watch_time
        from tb_user_video_log
        where year(start_time) = 2021) a 
    left join
        (select video_id, duration
        from tb_video_info) b
    on a.video_id = b.video_id
    group by a.video_id
) c
order by avg_comp_play_rate desc;