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

明确题意：

统计在有用户互动的最近一个月中，每类视频的转发量和转发率

问题分解：

关联用户-视频互动表和短视频信息表：left join tb_video_info tvi on tuvl.video_id = tvi.video_id

筛选最近一个月的记录：where date(start_time) > (select date_sub(max(start_time),interval 30 day) from tb_user_video_log)

按视频tag分组：group by tvi.tag

计算视频转发量：sum(tuvl.if_retweet)

计算视频转发率（转发率＝转发量÷播放量）：sum(tuvl.if_retweet) / count(*)

细节问题：

按转发率降序排序：order by retweet_rate desc

保留3位小数：round(x,3)

select tvi.tag,
round(sum(tuvl.if_retweet), 3) retweet_cut,
round(sum(tuvl.if_retweet) / count(*), 3) retweet_rate
from tb_user_video_log tuvl
left join tb_video_info tvi on tuvl.video_id = tvi.video_id
where date(start_time) > (
    select date_sub(max(start_time),interval 30 day)
    from tb_user_video_log
)
group by tvi.tag
order by retweet_rate desc;