题解：
1）首先给tb_user_log 表做初步的筛选和编号，方便后续做标记；
2）首先登录几天就有几个金币，第3、7天有额外金币；
3）利用中间表做编号，由于每隔7天就清零，还有换了账号就清零，所以必须case when来判断逻辑算金币；
需要达到的效果如下；
101|202107|2021-07-07|None|101|0.000
101|202107|2021-07-08|2021-07-07|101|1.000
101|202107|2021-07-09|2021-07-08|101|2.000
101|202107|2021-07-10|2021-07-09|101|3.000
101|202107|2021-07-11|2021-07-10|101|4.000
101|202107|2021-07-12|2021-07-11|101|5.000
101|202107|2021-07-13|2021-07-12|101|6.000
101|202107|2021-07-14|2021-07-13|101|0.000
101|202107|2021-07-15|2021-07-14|101|1.000
101|202107|2021-07-16|2021-07-15|101|2.000
102|202110|2021-10-01|None|101|3.000
102|202110|2021-10-02|2021-10-01|101|4.000
102|202110|2021-10-03|2021-10-02|101|5.000
102|202110|2021-10-05|2021-10-03|101|0.000
102|202110|2021-10-06|2021-10-05|101|1.000
最后贴上SQL代码，做了一天总算有所收获。
with tmp as(
select uid
,date(in_time)in_time
,row_number() over(partition by uid order by date(in_time))id
from tb_user_log where artical_id=0 and sign_in=1 and date(in_time)>='2021-07-07' and date(in_time)<'2021-11-01'
)
select
a.uid
,a.i_time
,sum(1)
+sum(case when a.id=2 then 2 else 0 end)
+sum(case when a.id=6 then 6 else 0 end)
from(
select a.uid
,date_format(a.in_time,'%Y%m')i_time
,@uid
,case when @uid=a.uid then case when a.in_time!=date_add(b.in_time,interval 1 day) or b.in_time is null
then @id:=0
else
case when @id<6
then @id:=@id+1
else @id:=0
end
end
when @uid<>a.uid then case when a.in_time!=date_add(b.in_time,interval 1 day) or b.in_time is null
then @id:=0
else
case when @id<6
then @id:=@id+1
else @id:=0
end
end
end id
from (select @id:=-1,@uid:=uid from tmp where id=1 limit 1) as init2,tmp a left join tmp b
on a.id=b.id+1 and a.uid=b.uid
)a group by a.uid,a.i_time
