题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
1.视频完播率：完成播放次数占总播放次数的比例。完成播放：结束观看时间与开始播放时间的差 >= 视频时长。
sum(if(tu.end_time-tu.start_time>=tv.duration,1,0))/count(*)
2.连接两张表
left join
3.根据video_id分组
group by video_id
select tu.video_id,round(sum(if(tu.end_time-tu.start_time>=tv.duration,1,0))/count(*),3) as avg_comp_play_rate from tb_user_video_log as tu left join tb_video_info as tv on tu.video_id = tv.video_id where year(tu.start_time) = 2021 group by tu.video_id order by avg_comp_play_rate desc
投递地平线等公司10个岗位