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

select * from(
select tag,'2021-10-01' as dt,sum(like_cnt) sum_like_cnt_7d,max(retweet_cnt) as max_retweet_cnt_7d
from (
select tag,date_format(start_time,'%Y-%m-%d') as day_,sum(if_like) as like_cnt,sum(if_retweet) retweet_cnt
from(
select a.*,b.tag from tb_user_video_log a left join tb_video_info b using(video_id)
) a
group by tag,day_
) d 
where timestampdiff(day,day_,'2021-10-01')<7 and day_<='2021-10-01'
group by tag,dt
union all
select tag,'2021-10-02' as dt,sum(like_cnt) sum_like_cnt_7d,max(retweet_cnt) as max_retweet_cnt_7d
from (
select tag,date_format(start_time,'%Y-%m-%d') as day_,sum(if_like) as like_cnt,sum(if_retweet) retweet_cnt
from(
select a.*,b.tag from tb_user_video_log a left join tb_video_info b using(video_id)
) a
group by tag,day_
) d 
where timestampdiff(day,day_,'2021-10-02')<7 and day_<='2021-10-02'
group by tag,dt
union all
select tag,'2021-10-03' as dt,sum(like_cnt) sum_like_cnt_7d,max(retweet_cnt) as max_retweet_cnt_7d
from (
select tag,date_format(start_time,'%Y-%m-%d') as day_,sum(if_like) as like_cnt,sum(if_retweet) retweet_cnt
from(
select a.*,b.tag from tb_user_video_log a left join tb_video_info b using(video_id)
) a
group by tag,day_
) d 
where timestampdiff(day,day_,'2021-10-03')<7 and day_<='2021-10-03'
group by tag,dt
) f
order by tag desc,dt asc