题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select
t2.video_id,
round(t2.s/t2.ss,3) avg_rate
from
(
select
t1.video_id,
sum(if(t1.diff >= t1.duration,1,0)) s,
count(*) ss
from
(select
tu.uid,
tu.video_id,
tu.end_time - tu.start_time diff,
tb.duration
from
tb_user_video_log tu
join
tb_video_info tb
on
tu.video_id = tb.video_id
where YEAR(start_time) = 2021
) t1
group by
t1.video_id) t2
order by avg_rate desc
”