/*计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序
理解题意：1.2021年有播放记录
2.完播行为：结束观看时间-开始观看时间 > 视频时长
*/
select a.video_id
,round(sum(case when a.time_long >= a.duration then 1 else 0 end )/count(start_time),3)as rate
from(
    select t.video_id, start_time, end_time
    ,timestampdiff(second,start_time,end_time) as time_long
    , i.duration
    from tb_user_video_log t
    left join tb_video_info i 
    on t.video_id = i.video_id
)a
where year(start_time) = 2021
group by a.video_id
order by 2 desc