打标签法

（为了详述标记过程，用层层子查询描述。）

第一层：

select distinct uid,
            date(in_time) as date,     
			#签到日期去重
			row_number() over(partition by uid order by date(in_time) asc) as uid_rk,    
			#分别为每个人的签到日期排名
            date(in_time)-row_number() over(partition by uid order by date(in_time) asc) as date_group  #日期减去排名数得到相同数字的即为连续签到
            from tb_user_log
            where artical_id =0
			and sign_in=1
            and date(in_time) between '2021-07-07' and '2021-10-31' 

第一层标签如图：

第二层：

select *,
        row_number() over(partition by date_group,uid order by date asc) as group_rk
		#在每个连续签到组中再进行排序，方便后续计算
        from 
            (select distinct uid,
            date(in_time) as date,
			row_number() over(partition by uid order by date(in_time) asc) as uid_rk,
            date(in_time)-row_number() over(partition by uid order by date(in_time) asc) as date_group
            from tb_user_log
            where artical_id =0
			and sign_in=1
            and date(in_time) between '2021-07-07' and '2021-10-31' 
            )t1

第三层：

select *,
    date_format(date,'%Y%m') as month,
	#给每次签到打上月份标签
    case group_rk%7
        when 3 then 3
        when 0 then 7
        else 1 end as day_coin
	#组中排名对7取余数，决定其领得金币数
    from 
        (select *,
        row_number() over(partition by date_group,uid order by date asc) as group_rk
        from 
            (select distinct uid,
            date(in_time) as date,
			row_number() over(partition by uid order by date(in_time) asc) as uid_rk,
            date(in_time)-row_number() over(partition by uid order by date(in_time) asc) as date_group
            from tb_user_log
            where artical_id =0
			and sign_in=1
            and date(in_time) between '2021-07-07' and '2021-10-31' 
            )t1
         )t2

最后代码：

select uid,
month,
sum(day_coin) as coin
#根据ID，月份聚合后，对分数求和即可得出每人每月金币总数
from 
    (select *,
    date_format(date,'%Y%m') as month,
    case group_rk%7
        when 3 then 3
        when 0 then 7
        else 1 end as day_coin
    from 
        (select *,
        row_number() over(partition by date_group,uid order by date asc) as group_rk
        from 
            (select distinct uid,
            date(in_time) as date,
			row_number() over(partition by uid order by date(in_time) asc) as uid_rk,
            date(in_time)-row_number() over(partition by uid order by date(in_time) asc) as date_group
            from tb_user_log
            where artical_id =0
			and sign_in=1
            and date(in_time) between '2021-07-07' and '2021-10-31' 
            )t1
         )t2
     )t3
group by uid,month
order by month asc,uid asc