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

SELECT tl.video_id,ROUND(SUM(CASE WHEN (end_time -start_time) >= duration  
                          THEN  1 
                          ELSE 0
                          END) / COUNT(*),3) as avg_comp_play_rate
FROM tb_user_video_log  tl JOIN tb_video_info  ti  on tl.video_id = ti.video_id  
WHERE year(tl.start_time) =2021
GROUP BY  tl.video_id
order by  avg_comp_play_rate desc