题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select a.video_id,
round(count(if(timestampdiff(second, start_time, end_time)>=duration, 1, null))/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 video_id
order by avg_comp_play_rate desc
