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

with t1 as (
    select tl.video_id,count(*) comp_play_cnt
    from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id
    where year(tl.start_time) = 2021
    and timestampdiff(second,tl.start_time,tl.end_time) >= ti.duration
    group by tl.video_id
),
t2 as (
    select video_id,count(*) total_play_cnt
    from tb_user_video_log
    where year(start_time) = 2021
    group by video_id
)
select t2.video_id,ifnull(round(t1.comp_play_cnt/t2.total_play_cnt,3),0) avg_comp_play_rate
from t2 left join t1 on t2.video_id = t1.video_id
where t2.video_id is not null
order by avg_comp_play_rate desc;