with totalcount as ( SELECT video_id, count() as totalcount from tb_user_video_log WHERE EXTRACT(YEAR from start_time) = '2021' GROUP BY video_id ), temp as ( select u.video_id, IF(UNIX_TIMESTAMP(u.end_time)-UNIX_TIMESTAMP(u.start_time)>=v.duration,'true','false') as status from tb_user_video_log u INNER JOIN tb_video_info v on u.video_id = v.video_id and EXTRACT(YEAR from start_time) = '2021' ), activeCount as ( SELECT video_id,count() as activeCount from temp WHERE status='true' GROUP BY video_id ), temp1 as ( select t.video_id, ROUND(a.activeCount/t.totalcount,3) as rate from totalcount t INNER JOIN activeCount a on t.video_id = a.video_id ), allVideoId as ( SELECT DISTINCT video_id from tb_user_video_log )

SELECT a.video_id, IFNULL(t.rate,0.000) as avg_comp_play_rate from allVideoId a LEFT JOIN temp1 t on a.video_id = t.video_id ORDER BY avg_comp_play_rate desc