题解 | #各个视频的平均完播率#

select uvl.video_id, 
round( sum(case when (end_time - start_time) >= duration then 1 else 0 end) / count(uvl.video_id), 3 ) as avg_comp_play_rate
from tb_user_video_log uvl
left outer join tb_video_info vi
on uvl.video_id = vi.video_id
where year(end_time) = 2021
group by uvl.video_id
order by avg_comp_play_rate desc;