题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select video_id,
round(sum(case when t3.x1 >= t3.duration then 1 else 0 end)/count(*),3) avg_comp_play_rate
from(
select t1.uid,t1.video_id,TIMESTAMPDIFF(SECOND,t1.start_time,t1.end_time) x1,
t2.video_id video_id2,t2.duration
from tb_user_video_log t1
left join tb_video_info t2 on t1.video_id = t2.video_id
where year(t1.start_time) = '2021'
)t3 group by t3.video_id
order by avg_comp_play_rate desc
查看18道真题和解析