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

SELECT
    u.video_id,
    round(
        (sum(
            if((TIMESTAMPDIFF(SECOND,start_time,end_time) >= duration),1,0)
            ) / COUNT(*)),3
        )avg_comp_play_rate
FROM
    tb_user_video_log u
JOIN
    tb_video_info i
ON
    u.video_id = i.video_id
WHERE
    year(u.start_time) = 2021
GROUP BY
    u.video_id
ORDER BY
    avg_comp_play_rate DESC
    

没啥说的就这样吧