题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select
distinct t2.video_id,
round(avg(if (timestampdiff(second,start_time,end_time) >= duration, 1, 0)) ,3)as avg_comp_play_rate
from
tb_video_info t1
inner join tb_user_video_log t2 on t1.video_id = t2.video_id
where YEAR(start_time)='2021' and YEAR(end_time)='2021'
group by
t2.video_id
order by avg_comp_play_rate desc
