题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select
a.video_id,
round(count(case when end_time-start_time>=duration then a.video_id end)/count(a.video_id),3) as avg_comp_play_rate
from
tb_user_video_log a
left join
tb_video_info b
on (a.video_id=b.video_id)
where year(start_time) = '2021'
group by a.video_id
order by avg_comp_play_rate desc
注：mysql里面的datediff()不能计算秒的差
查看5道真题和解析
哔哩哔哩公司福利 443人发布
