题解 | 各个视频的平均完播率
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
with t1 as(
select video_id,
timestampdiff(second,start_time,end_time)-duration as dif
from tb_user_video_log left join tb_video_info using(video_id)
where year(start_time)=2021
)
select video_id,
round(count(if(dif>=0,true,null))/count(*),3) as avg_comp_play_rate
from t1
group by video_id
order by avg_comp_play_rate desc
