一、问题拆解：
①数据底表tb1,是20210707-20211031，artical_id=0，sign_in=1的数据；
②7天一个周期，需要将周期进行编号，便于后续分组计算，方法是（（某日期-最早日期）/7+1）取整作为周期数；
③是否连续3天、连续7天签到判断，用窗口函数+窗口范围range interval n day preceding来实现。
二、解答步骤：
①先把数据底表tb1取好；
②根据tb1计算cycle，形成tb2；
③窗口函数sum根据uid、cycle进行分组，计算3天内、7天内移动累加值，形成tb3；
④判断：每个uid,月份,cycel分组内3天、7天移动累加值是否等于3和7，如果等于则表明可以获得额外的2分和6分，此时形成tb4；
⑤在tb4基础上进行最终取值和计算即可。

with tb1 as(
select uid, date(in_time) as sign_day, sign_in
from tb_user_log 
where artical_id=0 and sign_in=1 and date(in_time) between '2021-07-07' and '2021-10-31'
group by uid, date(in_time))

select uid, `month`, sum(coin)
from(
select uid, date_format(sign_day,'%Y%m') as `month`,
case when max(sign_num_3d)=3 and max(sign_num_7d)=7 then max(sign_num_7d)+8
when max(sign_num_3d)=3 and max(sign_num_7d)<7 then max(sign_num_7d)+2
else max(sign_num_7d) end as coin
from(
select uid, sign_day, cycle,
sum(sign_in)over(partition by cycle,uid order by sign_day range interval 2 day preceding) as sign_num_3d,
sum(sign_in)over(partition by cycle,uid order by sign_day range interval 6 day preceding) as sign_num_7d
from(
select uid, sign_day, 
floor(datediff(sign_day,min(sign_day)over(partition by uid))/7+1) cycle, 
sign_in
from tb1) as tb2
) as tb3
group by uid, `month`, cycle) as tb4
group by uid, `month`
order by uid, `month`