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

select t.video_id,
       round(t.overnum/t.num,3) as avg_comp_play_rate
from (
select a.video_id,
       count(a.video_id) as num,
       count(case when a.time>=b.duration then a.video_id end) as overnum
from
(select video_id,
        start_time,
        (unix_timestamp(end_time)-unix_timestamp(start_time)) as time
from tb_user_video_log) a
inner join tb_video_info b
on a.video_id=b.video_id
where YEAR(a.start_time)= '2021'
group by a.video_id) t
order by avg_comp_play_rate desc