题解 | 连续签到领金币

with a as (

select *,row_number() over(partition by uid,id-day(in_time) order by in_time) rn from tb_user_log

where sign_in=1 and where date(in_time) between '2021-07-07' and '2021-10-31'

select uid,DATE_FORMAT(in_time,'%Y%m') month,sum(case when rn%7=3 then 3 when  rn%7=0 then 7 else 1 end) coin

from a group by uid,month

order by month,uid;