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