题解 | #国庆期间每类视频点赞量和转发量#

# 每类视频每天的近一周总点赞量和一周内最大'单天'转发量
# between '2021-09-25' and '2021-10-01'
select tag,max(dt)dt,sum(like_cnt)sum_like_cnt_7d,max(retweet_cnt)max_retweet_cnt_7d
from (
select tag,date(start_time)dt,sum(if_like)like_cnt,sum(if_retweet)retweet_cnt from tb_user_video_log
join tb_video_info using(video_id)
where date(start_time) between '2021-09-25' and '2021-10-01'
group by 1,2
)a
group by 1
union
# between '2021-09-26' and '2021-10-02'
select tag,max(dt)dt,sum(like_cnt)sum_like_cnt_7d,max(retweet_cnt)max_retweet_cnt_7d
from (
select tag,date(start_time)dt,sum(if_like)like_cnt,sum(if_retweet)retweet_cnt from tb_user_video_log
join tb_video_info using(video_id)
where date(start_time) between '2021-09-26' and '2021-10-02'
group by 1,2
)a
group by 1
union
# between '2021-09-27' and '2021-10-03'
select tag,max(dt)dt,sum(like_cnt)sum_like_cnt_7d,max(retweet_cnt)max_retweet_cnt_7d
from (
select tag,date(start_time)dt,sum(if_like)like_cnt,sum(if_retweet)retweet_cnt from tb_user_video_log
join tb_video_info using(video_id)
where date(start_time) between '2021-09-27' and '2021-10-03'
group by 1,2
)a
group by 1
order by tag desc,dt