思路:题目中只有两张表,所以很简单的进行两表连接,但是考虑近一个月的问题需要日期相减函数
timestampdiff(day,date(start_time),date((select max(start_time) from tb_user_video_log))) <= 29
一个月内,也就是包括自己在内的29天。
第二个问题就是根据视频分类分组,不能通过视频Id分组。
group by tag
SELECT
tag,sum(if_retweet) as retweet_cnt,round(sum(if_retweet)/count(*),3) as retweet_rate
FROM tb_user_video_log as t1 JOIN tb_video_info as t2
ON t1.video_id = t2.video_id
WHERE
timestampdiff(day,date(t1.start_time),date((select max(start_time) from tb_user_video_log))) <= 29
GROUP BY
t2.tag
ORDER BY retweet_rate desc