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

# 2021年 有播放记录的
# 每个视频的完播率（三位小数）
# 完播率降序排序
with temp as(
    select
        log.video_id video_id, 
        round(sum(
            case when timestampdiff(second,log.start_time,log.end_time) >= video.duration then 1 else 0 end
        )/count(*),3) avg_comp_play_rate
    from tb_user_video_log log 
        join tb_video_info video on log.video_id = video.video_id
    where year(log.start_time)='2021'
    group by video.video_id
)
select 
    v.video_id, ifnull(temp.avg_comp_play_rate,round(0/2,3)) avg_comp_play_rate
from temp right join (select distinct video_id from tb_user_video_log log ) v on temp.video_id = v.video_id
order by avg_comp_play_rate desc;