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

WITH t1 AS (
    SELECT a.video_id,
           TIMESTAMPDIFF(SECOND,start_time,end_time) AS time_delta,
           b.duration
    FROM tb_user_video_log a
    LEFT JOIN tb_video_info b
    ON a.video_id = b.video_id
    WHERE SUBSTR(start_time,1,4) = '2021'
    HAVING time_delta >= duration
),
t2 AS (
    SELECT video_id,
           COUNT(video_id) AS wanboshu
    FROM t1
    GROUP BY video_id
),
t3 AS (
    SELECT video_id,
           COUNT(video_id) AS totalnum
    FROM tb_user_video_log
    WHERE SUBSTR(start_time,1,4) = '2021'
    GROUP BY video_id
),
t4 AS (
    SELECT t3.video_id,
           ROUND(IFNULL(wanboshu,0) / totalnum,3) AS avg_comp_play_rate
    FROM t3
    LEFT JOIN t2
    ON t3.video_id = t2.video_id
    ORDER BY avg_comp_play_rate DESC
)SELECT * FROM t4;

# 这道题的关键在于时间节点的筛选判断！