題解：
1）首先給tb_user_log 表做初步的篩選和編號，方便后續(xù)做標(biāo)記；
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