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

#解题思路

-- 先进行并表操作

-- 在根据用户，有互动，找出最近的一个时间点，然后以这个时间点往后推30天，用between and 来找出符合条件的筛选出来

-- 对转发量按照tag进行求和，转发率通过对结束时间进行记数可以得出

WITH t2 AS (
    SELECT
        MAX(DATE_FORMAT(end_time,'%Y-%m-%d')) AS decent_starttime
    FROM
        tb_user_video_log
    WHERE if_follow != 0 OR if_like != 0 OR if_retweet != 0 OR comment_id IS NOT NULL
),
t1 as
(
    SELECT
        a.if_retweet,
        a.end_time,
        b.tag
    FROM
        tb_user_video_log a
        JOIN tb_video_info b ON a.video_id = b.video_id
    WHERE 
		DATE_FORMAT(end_time,'%Y-%m-%d') 
		BETWEEN DATE_SUB((SELECT decent_starttime FROM t2), INTERVAL 29 DAY) 
		and (SELECT decent_starttime FROM t2)

)

SELECT
	  tag,
		sum(if_retweet) as retweet_cut,
		round(sum(if_retweet)/count(end_time),3) as retweet_rate		
FROM t1
group by tag
order by retweet_rate desc;