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

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