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

WITH t1 AS (
    SELECT a.video_id,
           b.video_id                                  AS user_watch_video_id,
           b.start_time,
           b.end_time,
           a.duration,
           substring(b.end_time,1,4) as watch_year,
           TIMESTAMPDIFF(SECOND, start_time, end_time) AS watch_second
           -- SUBSTRING(b.end_time, 1, 4) AS year1
    FROM tb_video_info a
             LEFT JOIN tb_user_video_log b ON a.video_id = b.video_id

    -- having year1 = "2021"
)
SELECT t1.video_id,
       ROUND(SUM(IF(t1.watch_second >= t1.duration, 1, 0)) / COUNT(t1.video_id), 3) AS avg_comp_play_rate
FROM t1 where t1.watch_year=2021
GROUP BY t1.video_id
ORDER BY avg_comp_play_rate DESC;