题解 | 各个视频的平均完播率
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select
s1.video_id,
coalesce(round(count(s2.video_id)/nullif(count(s1.video_id),0),3),0) avg_comp_play_rate
from
tb_user_video_log s1
left join
tb_video_info s2
on
s1.video_id = s2.video_id
and
timestampdiff(second,start_time,end_time) >= duration
where (year(start_time),year(end_time)) = (2021,2021)
group by 1
order by 2 desc
