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

select 
tag,
date as dt,
sum(like_cnt) as like_cnt,
max(retweet_cnt) as retweet_cnt
from 
(
select 
tag,
c.date,
date(start_time) as start_date,
sum(if_like) as like_cnt,
sum(if_retweet) as retweet_cnt
from tb_user_video_log a 
left join 
tb_video_info b 
on a.video_id = b.video_id 
join 
(
    select '2021-10-01' as date
    union all 
    select '2021-10-02' as date
    union all 
    select '2021-10-03' as date
) c 
on date(start_time) <= c.date
and date(start_time) > date_sub(c.date, interval 7 day)
group by 
tag,
c.date,
date(start_time)
) x
group by 
tag,
date
order by tag desc, date