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

WITH play_info AS (
    SELECT 
        u.uid, 
        u.video_id,
        v.duration,
        v.release_time,
        TIMESTAMPDIFF(SECOND, start_time, end_time) AS play_time,
        CASE WHEN (TIMESTAMPDIFF(SECOND, start_time, end_time) >= v.duration AND YEAR(u.start_time) = 2021) THEN 1 ELSE 0 END AS comp_play,
        CASE WHEN YEAR(u.start_time) = 2021 THEN 1 ELSE 0 END AS 2021_play
         -- 注意只统计2021年的播放和完播
    FROM tb_user_video_log u
    LEFT JOIN tb_video_info v
    ON u.video_id = v.video_id
)

SELECT 
    video_id,
    ROUND(SUM(comp_play) / SUM(2021_play), 3) AS avg_comp_play_rate
FROM play_info
GROUP BY video_id
ORDER BY avg_comp_play_rate DESC

一开始自测运行的时候可以通过，但是提交的时候两个用例全都不能通过，发现有坑：

1. 计算播放时长不能直接使用end_time - start_time，而是要使用TIMESTAMPDIFF()函数，选择"SECOND"为计算单位。
2. 题目中要求计算的是2021年的完播率，所以提交时用例里2003视频在2020年的完播则不能计入。因此完整播放只计算start_time在2021年并且超过视频总时长的，播放次数也只计算start_time在2021年的，这里使用了两个case when。
3. 保留三位小数，也是考点。
4. 按照完播率，降序排列。