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

WITH complet as(
    SELECT tb_user_video_log.video_id
    ,IF(timestampdiff(second,start_time,end_time) >= duration,1,0) com_play
    FROM tb_user_video_log
    JOIN tb_video_info 
    ON tb_user_video_log.video_id = tb_video_info.video_id
    WHERE year(start_time) = 2021
)
SELECT video_id
    ,round(avg(com_play),3) avg_com_play_rate
FROM complet
GROUP BY video_id
ORDER BY avg_com_play_rate desc