题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
WITH t AS ( SELECT t1.video_id, ( unix_timestamp(end_time) - unix_timestamp(start_time) ) as diff_second, t2.duration FROM tb_user_video_log AS t1 LEFT JOIN tb_video_info AS t2 ON t1.video_id = t2.video_id WHERE datediff(t1.start_time, '2021-01-01') >= 0 AND datediff(t1.end_time, '2021-01-01') >= 0 ) SELECT t.video_id, round( sum(if(t.diff_second - t.duration < 0, 0, 1)) / count(1), 3 ) AS avg_comp_play_rate FROM t GROUP BY t.video_id ORDER BY avg_comp_play_rate desc;
看题仔细点,小条件太多了