#2021
#有播放
#完播率 diff>= shiping shichang by shiping 
#round 3
#desc

with t1 as(
    select t.*,(case when timestampdiff(second,t.start_time,t.end_time) >= tv.duration then 1 else 0 end) as finish_stat
    from tb_user_video_log t left join tb_video_info tv on t.video_id= tv.video_id
    # where date(t.end_time)<='2021-12-31'
)

select t1.video_id, round(sum(t1.finish_stat)/count(*) ,3) as avg_comp_play_rate
from t1 left join tb_video_info tv on t1.video_id=tv.video_id
where date(t1.end_time)<='2021-12-31' and date(t1.end_time)>='2021-01-01'
group by t1.video_id
order by avg_comp_play_rate desc