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

with recursive t(dt) as (

SELECT CAST('2021-10-01'as DATE)

UNION all

SELECT dt+ interval 1 day

FROM t

WHERE dt<"2021-10-03")

,t1 as (

SELECT tag

    ,date(start_time) dt_temp

    ,SUM(if_like) like_cnt

    ,SUM(if_retweet) retweet_cnt

FROM tb_user_video_log

JOIN tb_video_info USING(video_id)

GROUP BY tag,date(start_time))

SELECT tag,dt,sum(like_cnt),max(retweet_cnt)

FROM t

LEFT JOIN t1

ON dt>=dt_temp and datediff(dt,dt_temp)<7

group by tag,dt