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

#3、按照输出需求挑选展示字段#
select t1.tag,
sum(t2.if_retweet) as retweet_cut,
round(sum(t2.if_retweet) / count(t2.start_time) ,3) as retweet_rate

#1、确定需求所需的数据表（tag👉tb_video_info；retweet_cut＆retweet_rate👉tb_user_video_log）#
from tb_video_info as t1
left join tb_user_video_log as t2
on t1.video_id = t2.video_id

#2、⭐对关联数据表进行条件筛选（用户互动的最近一个月=在各个tag的max(start_time)（数据转储日期=各个tag的最后播放日期）中，前30天内（不含）有播放的视频，筛选出符合条件的视频）#
where date(t2.start_time) 
> (select DATE_SUB(max(start_time), INTERVAL 30 DAY) from tb_user_video_log)

group by 1
order by 3 desc;