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

with t1t2 as (
    select t2.video_id, t1.start_time, t1.end_time, t2.duration
    from tb_user_video_log as t1
    left join tb_video_info as t2 using (video_id)
    WHERE YEAR(end_time)='2021'
)

select video_id, round(ifnull(sum(completed)/count(*),0),3) as completed_rate
from (
    select video_id
            , case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end as completed
    from t1t2
)t3
group by video_id
order by completed_rate desc