题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
# 条件：2021年，则 SUBSTR(start_time,1,4)='2021'
# tb_user_video_log left join tb_video_info，拿到视频时长
# 视频id分组，如果播放时长>=视频时长，则算完播
select video_id
,round(sum(case when df>=duration then 1 else 0 end)/count(id),3) as avg_comp_play_rate
from (
select tvl.*
,tvi.duration
,timediff(end_time,start_time) as df
from tb_user_video_log as tvl
left join tb_video_info as tvi
on tvl.video_id=tvi.video_id
where SUBSTR(start_time,1,4)=2021
) as a
group by video_id
order by avg_comp_play_rate desc