-- MySQL 答案
select
    *
from
    (
        SELECT
            b.tag,
            date_format (a.start_time, '%Y-%m-%d') as dt,
            SUM(sum(a.if_like)) over (
                partition by
                    tag
                order by
                    date_format (a.start_time, '%Y-%m-%d')  ROWS between 6 PRECEDING 
			  	and current row
            ) as like_cnt,
            max(sum(a.if_retweet)) over (
                partition by
                    tag
                order by
                    date_format (a.start_time, '%Y-%m-%d')  ROWS between 6 PRECEDING
			  	and current row
            ) as ret_cnt
        FROM
            tb_user_video_log a
            LEFT JOIN tb_video_info b ON a.video_id = b.video_id
        WHERE
            DATEDIFF (
                '2021-10-03',
                date_format (a.start_time, '%Y-%m-%d')
            ) < 9
        group by
            b.tag,
            date_format (a.start_time, '%Y-%m-%d')
        order by
            b.tag desc,
            date_format (a.start_time, '%Y-%m-%d') asc
    ) t
where
    t.dt >= '2021-10-01'
    and t.dt <= '2021-10-03'



-- SQL SERVER答案
select
*
 from
        (
            SELECT
                b.tag,
                CONVERT(varchar(10), a.start_time, 120) as dt,
                SUM(sum(a.if_like)) over (
                    partition by
                        tag
                    order by
                        CONVERT(varchar(10), a.start_time, 120) asc ROWS BETWEEN 6 PRECEDING
                        AND CURRENT ROW
                ) as like_cnt,
                max(sum(a.if_retweet)) over (
                    partition by
                        tag
                    order by
                        CONVERT(varchar(10), a.start_time, 120) asc ROWS BETWEEN 6 PRECEDING
                        AND CURRENT ROW
                ) as ret_cnt
            FROM
                tb_user_video_log a
                LEFT JOIN tb_video_info b ON a.video_id = b.video_id
            WHERE
                DATEDIFF (
                    day,
                    CONVERT(varchar(10), a.start_time, 120),
                    '2021-10-03'
                ) < 9
            group by
                b.tag,
                CONVERT(varchar(10), a.start_time, 120)
        ) t
    where
        t.dt >= '2021-10-01'
        and t.dt <= '2021-10-03'
	order by 
                t.tag desc,
                t.dt asc