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

WITH t1 AS(
    SELECT tu.video_id, count(tu.video_id) AS total_count
    FROM tb_user_video_log tu
    LEFT JOIN tb_video_info tv
    ON tu.video_id = tv.video_id
    WHERE SUBSTR(start_time,1,4) = '2021'
    GROUP BY tu.video_id
),
t2 AS (
    SELECT tu.video_id,count(tu.video_id) AS valid_count
    FROM tb_user_video_log tu
    LEFT JOIN tb_video_info tv
    ON tu.video_id = tv.video_id
    WHERE end_time - start_time >= duration AND SUBSTR(start_time,1,4) = '2021'
    GROUP BY tu.video_id
)
SELECT t1.video_id,
       IFNULL(ROUND(t2.valid_count / t1.total_count,3),0.000) AS avg_comp_play_rate
FROM t1 
LEFT JOIN t2
ON t1.video_id = t2.video_id
GROUP BY t1.video_id
ORDER BY avg_comp_play_rate DESC;

# 注意：在统计的时候2021是一个不可获取的条件！