with T_tot_per_video as ( select tv.*, count(tu.uid) as tot_vue_perv,sum(coalesce(tu.if_retweet,0)) as tot_retweet_perv from tb_video_info tv left join tb_user_video_log tu on tv.video_id=tu.video_id where datediff(date((select max(start_time) from tb_user_video_log)), date(tu.start_time))<=29 group by tv.video_id ) select tpv.tag as tag , round(sum(tpv.tot_retweet_perv),3) as retweet_cnt, round(sum(tpv.tot_retweet_perv)/sum(tpv.tot_vue_perv),3) as retweet_rate from T_tot_per_video tpv group by tag order by retweet_rate desc