#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