题解 | #每类视频近一个月的转发量/率#
每类视频近一个月的转发量/率
https://www.nowcoder.com/practice/a78cf92c11e0421abf93762d25c3bfad
SELECT
tag,
sum( if_retweet ),
avg( if_retweet )
FROM
(
SELECT
a.video_id,
b.tag,
a.start_time,
a.end_time,
if_retweet
FROM
tb_user_video_log a
JOIN tb_video_info b ON a.video_id = b.video_id
) C
JOIN ( SELECT video_id, max( end_time ) max_time, min( start_time ) min_time FROM tb_user_video_log GROUP BY video_id ) D ON C.video_id = D.video_id
WHERE
timestampdiff( DAY, C.start_time, max_time )<= 29
GROUP BY
tag
