# 完播的定义:(结束时间-开始时间) > 视频时长 = 1 else 0
# 完播率: 完成播放次数 / 总次数

# 筛选条件: 2021年有播放记录,那么就是 year(start_time) = 2021
# 第一步,筛选出来了2021年所有视频的播放情况,完播率计算到新的列里面
with new_table as (
select l.video_id,
        i.duration,
       (case when (timestampdiff(second,l.start_time,l.end_time) - i.duration) >= 0 then 1 else 0 end) as wanbo
    from tb_user_video_log l
    join tb_video_info i on l.video_id = i.video_id
    where year(l.start_time) = 2021

)

# 第二步,完播数量之和/总播放量

select video_id, 
       round(sum(wanbo)/count(*),3) as avg_comp_play_rate
from new_table
group by video_id
order by avg_comp_play_rate desc