题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
select *
from (
SELECT
tag,
dt,
sum( tmp.like_cn ) over ( PARTITION BY tag ORDER BY dt desc rows BETWEEN current ROW AND 6 following ) as sum_li ,
max(tmp.re_cn) over ( PARTITION BY tag ORDER BY dt desc rows BETWEEN current ROW AND 6 following ) as max_re
FROM
(
SELECT
tag,
date_format( start_time, "%Y-%m-%d" ) dt,
sum( if_like ) like_cn,
sum( if_retweet ) re_cn
FROM
tb_user_video_log tl
JOIN tb_video_info ti ON tl.video_id = ti.video_id
WHERE
start_time BETWEEN '2021-09-25'
AND '2021-10-09'
GROUP BY
tag,
date_format( start_time, "%Y-%m-%d" )) tmp ) tmp1
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt
