题解 | 每类视频近一个月的转发量/率

SELECT tag,COUNT(CASE WHEN if_retweet=1 THEN tb.video_id END) AS retweet_cnt,
      ROUND(COUNT(CASE WHEN if_retweet=1 THEN tb.video_id END)/COUNT(tb.video_id),3) AS retweet_rate
FROM tb_user_video_log tb 
LEFT JOIN tb_video_info t ON tb.video_id=t.video_id
WHERE DATEDIFF(DATE((SELECT MAX(start_time) FROM tb_user_video_log)),DATE(start_time)) BETWEEN 0 AND 29
GROUP BY tag
ORDER BY retweet_rate DESC