# 暴力解题思路：
# 1、连接两表，保留tag start_time if_kile if_retweet字段
# 2、提取2021-10-01近七天的日期，用tag分组，计算sum(if_like) max(if_retweet)
# 3、将2021-10-01 2021-10-02 2021-10-03用union连接起来
# 4、按照视频类别降序、日期升序
select t2.tag,t2.dt,t2.sum_like_cnt_7d,t2.max_retweet_cnt_7d from (
# 2021-10-01查询结果
select t1.tag,'2021-10-01' as dt,sum(t1.sum_like_per_date) as sum_like_cnt_7d,
max(t1.sum_retweet_per_date) as max_retweet_cnt_7d from (
select distinct v1.tag,date_format(u1.start_time,'%Y-%m-%d') as start_time,
    sum(u1.if_retweet) over (partition by v1.tag,date_format(u1.start_time,'%Y-%m-%d')) as sum_retweet_per_date,
    sum(u1.if_like) over (partition by v1.tag,date_format(u1.start_time,'%Y-%m-%d')) as sum_like_per_date
    from 
tb_user_video_log as u1 left join  tb_video_info as v1 
on u1.video_id=v1.video_id 
    where datediff('2021-10-01',u1.start_time)<7 
and datediff('2021-10-01',u1.start_time)>=0
) t1 group by t1.tag )t2
union 
# 2021-10-02查询结果
select t1.tag,'2021-10-02' as dt,sum(t1.sum_like_per_date) as sum_like_cnt_7d,
max(t1.sum_retweet_per_date) as max_retweet_cnt_7d from (
select distinct v1.tag,date_format(u1.start_time,'%Y-%m-%d') as start_time,
    sum(u1.if_retweet) over (partition by v1.tag,date_format(u1.start_time,'%Y-%m-%d')) as sum_retweet_per_date,
    sum(u1.if_like) over (partition by v1.tag,date_format(u1.start_time,'%Y-%m-%d')) as sum_like_per_date
    from 
tb_user_video_log as u1 left join  tb_video_info as v1 
on u1.video_id=v1.video_id 
    where datediff('2021-10-02',u1.start_time)<7 
and datediff('2021-10-02',u1.start_time)>=0
) t1 group by t1.tag 
union 
# 2021-10-03查询结果
select t1.tag,'2021-10-03' as dt,sum(t1.sum_like_per_date) as sum_like_cnt_7d,
max(t1.sum_retweet_per_date) as max_retweet_cnt_7d from (
select distinct v1.tag,date_format(u1.start_time,'%Y-%m-%d') as start_time,
    sum(u1.if_retweet) over (partition by v1.tag,date_format(u1.start_time,'%Y-%m-%d')) as sum_retweet_per_date,
    sum(u1.if_like) over (partition by v1.tag,date_format(u1.start_time,'%Y-%m-%d')) as sum_like_per_date
    from 
tb_user_video_log as u1 left join  tb_video_info as v1 
on u1.video_id=v1.video_id 
    where datediff('2021-10-03',u1.start_time)<7 
and datediff('2021-10-03',u1.start_time)>=0
) t1 group by t1.tag 
order by tag desc,dt asc