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

SELECT
  tag,
  SUM(if_retweet) AS retweet_cnt,
  ROUND(SUM(if_retweet) / COUNT(*), 3) AS retweet_rate
FROM
  tb_video_info info
  INNER JOIN tb_user_video_log log ON info.video_id = log.video_id
WHERE
  DATEDIFF(
    (
      SELECT
        MAX(start_time)
      FROM
        tb_user_video_log
    ),
    start_time
  ) <= 29
GROUP BY
  tag
ORDER BY
  retweet_rate DESC

知识点：

1. MySQL DATEDIFF 函数计算两个 DATE、DATETIME 或 TIMESTAMP 值之间的天数