题解 | #每类视频近一个月的转发量/率#
每类视频近一个月的转发量/率
https://www.nowcoder.com/practice/a78cf92c11e0421abf93762d25c3bfad
#解题思路
-- 先进行并表操作
-- 在根据用户，有互动，找出最近的一个时间点，然后以这个时间点往后推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;
爱奇艺公司氛围 53人发布
