题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11?tpId=268&tqId=2285070&ru=/exam/company&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Fcompany
方法一：
WITH t1 AS (
SELECT tag,DATE_FORMAT(start_time,'%Y-%m-%d') dt,
SUM(SUM(if_like))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS sum_like_cnt_7d ,
MAX(SUM(if_retweet))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS max_retweet_cnt_7d
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
GROUP BY dt,tag
)
SELECT * FROM t1 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt ASC;
方法二：
select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from
(select tag,dt,
sum(like_cnt) over (partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
max(retweet_cnt) over (partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from (
select b.tag,DATE_FORMAT(a.start_time,'%Y-%m-%d') as dt,
sum(a.if_like) as like_cnt,
sum(a.if_retweet) as retweet_cnt
from tb_user_video_log a
join tb_video_info b
on a.video_id=b.video_id
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
group by tag,dt
order by tag,dt
) tt
order by tag desc,dt asc)aa
where dt between '2021-10-01' and '2021-10-03'
