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

# 先找到近一个月每个视频的播放量和转发量
with t1 as (
    select video_id, count(*) as watch_cnt, sum(if_retweet) as retweet_cnt
    from tb_user_video_log
    where date_format(end_time, '%Y-%m-%d') >= (select date_sub(max(date_format(end_time, '%Y-%m-%d')), interval 29 day) from tb_user_video_log)
    group by video_id
)# 两表连接，计算每类视频的转发量和转发率
select a.tag, sum(t1.retweet_cnt) as retweet_cut, round(sum(t1.retweet_cnt) / sum(watch_cnt), 3) as retweet_rate
from tb_video_info as a
right join t1
on t1.video_id = a.video_id
group by a.tag
order by retweet_rate desc;