题解 | #计算用户的平均次日留存率#

select *
from
(
select tag,dt,
sum(like_cnt) over (partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d,
max(retweet_cnt) over (partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d
from(
select tag,date(start_time) as dt,
sum(if_like) as like_cnt,sum(if_retweet) as retweet_cnt
from tb_user_video_log tuvl left join tb_video_info tvi
on tuvl.video_id = tvi.video_id
WHERE DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'#2021年，另外时间不连续怎么办
group by tag,dt
order by tag,dt 
) q
order by tag desc,dt
) qq
where dt between '2021-10-01' and '2021-10-03'

虽然代码通过了但是并不具有通用性

如何解决：如果数据量不大的话，如何保证至少每个类别下国庆头3天及之前一周的每天都有播放记录。