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

# 找出有用户互动的最近一个月
# 先找具体日期 date(uv_log.start_time)

# 每类视频的转发量和转发率（保留3位小数）。
select 
    vi.tag
    , sum(uv_log.if_retweet) as retweet_cut
    , round(avg(uv_log.if_retweet), 3) as retweet_rate
from tb_user_video_log as uv_log
right join tb_video_info as vi on uv_log.video_id = vi.video_id
where date(uv_log.start_time) between date_sub((select max(date(start_time)) from tb_user_video_log), interval 29 day) 
                                    and (select max(date(start_time)) from tb_user_video_log)
group by vi.tag
order by retweet_rate desc;