题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select p.video_id
,round(sum(p.compplay)/count(p.video_id),3) as avg_com_play_rate
from
(select t1.video_id
,case when (t1.end_time - t1.start_time) >= duration then 1
else 0
end as compplay
from tb_user_video_log as t1 left JOIN tb_video_info as t2
on t1.video_id = t2.video_id
where year(t1.start_time) = 2021
) as p
group by p.video_id
order by avg_com_play_rate DESC