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

# 计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序
# 🤔️：完播率=完成播放次数/总播放次数。 结束观看时间-开始播放时间>=视频时长视为完播

# 表一：链接&构造是否完播字段
with table1 as(
    select uid,video_id,
    (case when timestampdiff(second,start_time,end_time) >= duration then 1
    else 0 end) as if_wb
    from tb_user_video_log left join tb_video_info using(video_id)
    where year(start_time) = 2021
)
# 表二：构造完播率，完播率=完成播放次数/总播放次数
select video_id,round((sum(if_wb)/count(if_wb)),3) as avg_comp_play_rate
from table1
group by video_id
order by avg_comp_play_rate desc