with temp1 as (
 select a.*,b.tag from tb_user_video_log a INNER JOIN tb_video_info b USING(video_id)
),
temp2 as (
    select (max(start_time)-INTERVAL 29 day) as minDate from tb_user_video_log
),
temp3 as (
    select * from temp1 JOIN temp2
),
temp4 as (
select tag, sum(if_retweet) as retweet_cut,ROUND((sum(if_retweet)/count(start_time)),3) as retweet_rate from temp3 WHERE start_time >= minDate GROUP BY tag
),
temp5 as (SELECT DISTINCT tag from tb_video_info),
temp6 as (
 SELECT a.tag,b.retweet_cut, b.retweet_rate from temp5 a LEFT JOIN temp4 b on a.tag = b.tag ORDER BY retweet_rate desc
)

SELECT tag,IFNULL(retweet_cut,0) as retweet_cut,IFNULL(retweet_rate,0.000) as retweet_rate from temp6