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

select c.video_id,(case when f_rate is null then 0.000 else f_rate end) f_rate from (
    select distinct video_id from tb_user_video_log
)
 c left join(
select video_id,round(sum(case when v_gap>=duration then 1 else 0 end)/count(*),3) f_rate from(
select g.id,g.video_id,start_time,timestampdiff(second,start_time,end_time) as v_gap,duration,release_time from tb_user_video_log g left join tb_video_info i on g.video_id = i.video_id
)a
where start_time like '2021%'
group by video_id 
)b on b.video_id = c.video_id
order by f_rate desc