牛客SQL专项练习|SQL158.每类视频近一个月的转发量

SQL158.每类视频近一个月的转发量

题目链接

1. 有用户互动的最近一个月是指，播放时间与最近的播放时间相差一个月之内，也就是max(start_time) - 29
2. 统计在有用户互动的最近一个月每类视频的转发量，并不是指每类视频的max(start_time)-29.而是整体日期的max(start_time)-29

select tag,sum(if_retweet) as retweet_cut ,


round(avg(if_retweet),3) as retweet_rate

from tb_user_video_log as a inner join tb_video_info as b on a.video_id=b.video_id
where TIMESTAMPDIFF(day,start_time,(select max(start_time) from tb_user_video_log) )<30
group by tag
order by retweet_rate desc;

还有一个需要注意的地方是：

where TIMESTAMPDIFF(day,start_time,(select max(start_time) from tb_user_video_log) )<30
## select max(start_time) from tb_user_video_log语句一定要加上括号