题解 | 各个视频的平均完播率
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select
video_id,
round(sum(ifcomp)/count(distinct uid),3) avg_comp_play_rate
from(
select
v.video_id,
v.uid,
if(timestampdiff(second,v.start_time,v.end_time)>=i.duration,1,0) ifcomp
from
tb_user_video_log v left join tb_video_info i
using(video_id)
where left(v.start_time,4)='2021'
) t
group by
video_id
order by
avg_comp_play_rate desc
查看6道真题和解析