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

/*
SELECT u.video_id
 ,ROUND
  (AVG
   (CASE WHEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) >= i.duration THEN 1 ELSE 0 END)
    ,3) AS avg_comp_play_rate
 FROM tb_user_video_log AS u
INNER JOIN tb_video_info AS i ON u.video_id = i.video_id
WHERE year(u.start_time) = 2021
AND year(u.end_time) = 2021
GROUP BY u.video_id
ORDER BY avg_comp_play_rate DESC;
*/


SELECT
    u.video_id,
    ROUND(
        AVG(
            CASE WHEN TIMESTAMPDIFF(SECOND,u.start_time,u.end_time) >= v.duration
                THEN 1 ELSE 0 END
        )
    ,3) AS avg_comp_play_rate
FROM tb_user_video_log AS u
INNER JOIN tb_video_info AS v
    ON (u.video_id = v.video_id)
WHERE start_time >= '2021-01-01'
    AND start_time < '2022-01-01'
GROUP BY u.video_id
ORDER BY avg_comp_play_rate DESC;